CREATE TABLE tenants (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, slug VARCHAR(120) NOT NULL UNIQUE, trade_type VARCHAR(120) NOT NULL, region VARCHAR(80) NOT NULL, status VARCHAR(50) NOT NULL DEFAULT 'trialing', owner_user_id INT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE subscription_plans (id INT AUTO_INCREMENT PRIMARY KEY, plan_key VARCHAR(80) NOT NULL UNIQUE, name VARCHAR(120) NOT NULL, price_monthly DECIMAL(12,2) NOT NULL, included_seats INT NOT NULL, job_limit INT NOT NULL, ai_credits INT NOT NULL, storage_gb INT NOT NULL, features JSON, status VARCHAR(50) NOT NULL DEFAULT 'active', created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE tenant_subscriptions (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, plan_key VARCHAR(80) NOT NULL, status VARCHAR(50) NOT NULL, billing_provider VARCHAR(50) NOT NULL DEFAULT 'manual_platform_billing', external_customer_id VARCHAR(190) NULL, external_subscription_id VARCHAR(190) NULL, trial_ends_at DATETIME NULL, current_period_ends_at DATETIME NULL, cancel_at DATETIME NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE tenant_usage_counters (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, usage_key VARCHAR(80) NOT NULL, used_value DECIMAL(12,2) NOT NULL DEFAULT 0, limit_value DECIMAL(12,2) NOT NULL DEFAULT 0, period_start DATE NOT NULL, period_end DATE NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE tenant_invitations (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, email VARCHAR(190) NOT NULL, role VARCHAR(50) NOT NULL, token_hash VARCHAR(255) NOT NULL, invited_by_user_id INT NOT NULL, accepted_at DATETIME NULL, expires_at DATETIME NOT NULL, created_at DATETIME NOT NULL);
CREATE TABLE tenant_feature_flags (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, feature_key VARCHAR(120) NOT NULL, enabled TINYINT(1) NOT NULL DEFAULT 0, source VARCHAR(50) NOT NULL DEFAULT 'plan', created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE billing_events (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, provider VARCHAR(50) NOT NULL, provider_event_id VARCHAR(190) NOT NULL, event_type VARCHAR(120) NOT NULL, processed_at DATETIME NULL, payload_hash VARCHAR(255) NOT NULL, created_at DATETIME NOT NULL, UNIQUE KEY billing_events_provider_event_unique (provider, provider_event_id));
CREATE TABLE platform_billing_overrides (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL UNIQUE, override_mode VARCHAR(80) NOT NULL, override_amount DECIMAL(12,2) NULL, override_reason TEXT NULL, updated_by VARCHAR(190) NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, email VARCHAR(190) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, role VARCHAR(50) NOT NULL, name VARCHAR(190) NULL, phone VARCHAR(60) NULL, status VARCHAR(50) NOT NULL DEFAULT 'active', created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE clients (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, owner_user_id INT NOT NULL, name VARCHAR(190) NOT NULL, company_name VARCHAR(190), email VARCHAR(190), phone VARCHAR(60), address_line_1 VARCHAR(190), address_line_2 VARCHAR(190), town_city VARCHAR(120), postcode VARCHAR(40), notes TEXT, status VARCHAR(50) NOT NULL DEFAULT 'active', created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE jobs (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, owner_user_id INT NOT NULL, client_id INT NOT NULL, assigned_user_id INT NULL, assigned_crew_id INT NULL, title VARCHAR(190) NOT NULL, description TEXT, status VARCHAR(50) NOT NULL, priority VARCHAR(50) NOT NULL, scheduled_date DATE, scheduled_time TIME, address VARCHAR(255), notes TEXT, blocker_reason TEXT, workflow_stage VARCHAR(80) NOT NULL DEFAULT 'intake', customer_visible_status VARCHAR(190) NOT NULL DEFAULT 'Request received', completion_percent TINYINT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE appointments (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, created_by_user_id INT NULL, appointment_uid VARCHAR(80) NOT NULL, type VARCHAR(40) NOT NULL, title VARCHAR(190) NOT NULL, client_source VARCHAR(40) NOT NULL DEFAULT 'existing', client_status VARCHAR(40) NOT NULL DEFAULT 'active', client_name VARCHAR(190) NOT NULL, potential_client_email VARCHAR(190) NULL, potential_client_phone VARCHAR(60) NULL, appointment_date DATE NOT NULL, start_time TIME NOT NULL, end_time TIME NOT NULL, duration_minutes INT NOT NULL DEFAULT 60, technician_id INT NULL, technician_name VARCHAR(190) NOT NULL, location VARCHAR(255) NULL, notes TEXT NULL, status VARCHAR(50) NOT NULL DEFAULT 'booked', created_by_demo TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, KEY appointments_tenant_date_idx (tenant_id, appointment_date, start_time), KEY appointments_tenant_technician_idx (tenant_id, technician_id, appointment_date));
CREATE TABLE job_checklist_items (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, job_id INT NOT NULL, label VARCHAR(190) NOT NULL, category VARCHAR(80) NOT NULL, is_required TINYINT(1) NOT NULL DEFAULT 1, completed_at DATETIME NULL, completed_by_user_id INT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE job_timeline_events (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, job_id INT NOT NULL, actor_user_id INT NULL, event_type VARCHAR(80) NOT NULL, summary VARCHAR(255) NOT NULL, metadata JSON, created_at DATETIME NOT NULL);
CREATE TABLE job_field_notes (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, job_id INT NOT NULL, note_text TEXT NOT NULL, created_by INT NOT NULL, updated_by INT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NULL, is_deleted TINYINT(1) NOT NULL DEFAULT 0);
CREATE TABLE job_field_note_versions (id INT AUTO_INCREMENT PRIMARY KEY, field_note_id INT NOT NULL, previous_text TEXT NOT NULL, changed_by INT NOT NULL, changed_at DATETIME NOT NULL);
CREATE TABLE maintenance_agreements (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, owner_user_id INT NOT NULL, title VARCHAR(190) NOT NULL, client_name VARCHAR(190) NOT NULL, frequency VARCHAR(40) NOT NULL, status VARCHAR(40) NOT NULL DEFAULT 'active', next_run_date DATE NOT NULL, address VARCHAR(255) NULL, assigned_user_id INT NULL, notes TEXT NULL, generated_job_count INT NOT NULL DEFAULT 0, last_generated_job_id INT NULL, last_generated_at DATETIME NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE crews (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, owner_user_id INT NOT NULL, name VARCHAR(190) NOT NULL, lead_user_id INT NOT NULL, member_user_ids TEXT NULL, skill_tags TEXT NULL, availability_days TEXT NULL, status VARCHAR(40) NOT NULL DEFAULT 'active', created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE inventory_items (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, sku VARCHAR(80), name VARCHAR(190) NOT NULL, unit VARCHAR(40) NOT NULL DEFAULT 'each', reorder_point DECIMAL(10,2) NOT NULL DEFAULT 0, default_unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0, status VARCHAR(50) NOT NULL DEFAULT 'active', created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE truck_stock (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, inventory_item_id INT NOT NULL, assigned_user_id INT NOT NULL, quantity_on_hand DECIMAL(10,2) NOT NULL DEFAULT 0, updated_at DATETIME NOT NULL);
CREATE TABLE customer_portal_tokens (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, client_id INT NOT NULL, job_id INT NULL, quote_id INT NULL, invoice_id INT NULL, token_hash VARCHAR(255) NOT NULL, token_nonce VARCHAR(80) NULL, purpose VARCHAR(80) NOT NULL, expires_at DATETIME NOT NULL, used_at DATETIME NULL, last_accessed_at DATETIME NULL, revoked_at DATETIME NULL, revoked_by INT NULL, created_by INT NULL, created_at DATETIME NOT NULL);
CREATE TABLE customer_message_logs (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, client_id INT NOT NULL, job_id INT NOT NULL, portal_token_id INT NULL, channel VARCHAR(20) NOT NULL, recipient VARCHAR(190) NOT NULL, subject VARCHAR(190) NULL, body TEXT NOT NULL, provider_status VARCHAR(40) NOT NULL, provider_message VARCHAR(255) NULL, created_by INT NULL, created_at DATETIME NOT NULL);
CREATE TABLE automation_rules (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, name VARCHAR(190) NOT NULL, trigger_event VARCHAR(120) NOT NULL, action_type VARCHAR(120) NOT NULL, is_enabled TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE saved_reports (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, owner_user_id INT NOT NULL, name VARCHAR(190) NOT NULL, report_type VARCHAR(80) NOT NULL, filters JSON, schedule VARCHAR(80), created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE report_delivery_jobs (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, saved_report_id INT NOT NULL, requested_by_user_id INT NOT NULL, delivery_channel VARCHAR(40) NOT NULL DEFAULT 'email', recipient VARCHAR(190), status VARCHAR(60) NOT NULL, scheduled_for DATETIME NOT NULL, delivered_at DATETIME NULL, provider_message TEXT, metadata JSON, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE accounting_connections (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, provider_key VARCHAR(80) NOT NULL, external_tenant_id VARCHAR(190) NOT NULL, display_name VARCHAR(190), status VARCHAR(50) NOT NULL DEFAULT 'pending_oauth', token_ciphertext TEXT NULL, refresh_token_ciphertext TEXT NULL, scopes TEXT NULL, connected_by_user_id INT NOT NULL, connected_at DATETIME NULL, revoked_at DATETIME NULL, last_sync_at DATETIME NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE accounting_sync_jobs (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, accounting_connection_id INT NOT NULL, entity_type VARCHAR(80) NOT NULL, local_entity_id INT NULL, external_entity_id VARCHAR(190) NULL, direction VARCHAR(30) NOT NULL, status VARCHAR(50) NOT NULL DEFAULT 'queued', idempotency_key VARCHAR(190) NOT NULL, error_message TEXT NULL, attempts INT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE accounting_sync_logs (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, accounting_connection_id INT NOT NULL, accounting_sync_job_id INT NULL, level VARCHAR(30) NOT NULL, message VARCHAR(255) NOT NULL, metadata JSON, created_at DATETIME NOT NULL);
CREATE TABLE calendar_connections (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, provider_key VARCHAR(80) NOT NULL, external_account_id VARCHAR(190) NOT NULL, external_calendar_id VARCHAR(190) NOT NULL, display_name VARCHAR(190), sync_direction VARCHAR(40) NOT NULL DEFAULT 'two_way', status VARCHAR(50) NOT NULL DEFAULT 'pending_oauth', token_ciphertext TEXT NULL, refresh_token_ciphertext TEXT NULL, scopes TEXT NULL, sync_token TEXT NULL, delta_link TEXT NULL, webhook_channel_id VARCHAR(190) NULL, webhook_resource_id VARCHAR(190) NULL, webhook_secret_hash VARCHAR(255) NULL, connected_by_user_id INT NOT NULL, connected_at DATETIME NULL, revoked_at DATETIME NULL, last_inbound_sync_at DATETIME NULL, last_outbound_sync_at DATETIME NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE calendar_event_links (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, calendar_connection_id INT NOT NULL, job_id INT NOT NULL, provider_event_id VARCHAR(190) NOT NULL, provider_etag VARCHAR(190) NULL, provider_change_key VARCHAR(190) NULL, sync_status VARCHAR(50) NOT NULL DEFAULT 'linked', conflict_status VARCHAR(50) NOT NULL DEFAULT 'clean', last_fieldops_hash VARCHAR(255) NULL, last_provider_hash VARCHAR(255) NULL, last_synced_at DATETIME NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE calendar_sync_jobs (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, calendar_connection_id INT NOT NULL, job_id INT NULL, provider_event_id VARCHAR(190) NULL, direction VARCHAR(30) NOT NULL, action VARCHAR(80) NOT NULL, status VARCHAR(50) NOT NULL DEFAULT 'queued', idempotency_key VARCHAR(190) NOT NULL, error_message TEXT NULL, attempts INT NOT NULL DEFAULT 0, not_before_at DATETIME NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE calendar_webhook_events (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, calendar_connection_id INT NULL, provider_key VARCHAR(80) NOT NULL, provider_notification_id VARCHAR(190) NOT NULL, notification_type VARCHAR(120) NOT NULL, channel_id VARCHAR(190) NULL, resource_id VARCHAR(190) NULL, payload_hash VARCHAR(255) NOT NULL, received_at DATETIME NOT NULL, processed_at DATETIME NULL, status VARCHAR(50) NOT NULL DEFAULT 'received');
CREATE TABLE llm_connections (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, provider_key VARCHAR(80) NOT NULL, display_name VARCHAR(190) NOT NULL, model VARCHAR(190) NOT NULL, endpoint_url VARCHAR(255) NULL, api_key_ciphertext TEXT NULL, status VARCHAR(50) NOT NULL DEFAULT 'pending_verification', connected_by_user_id INT NOT NULL, last_verified_at DATETIME NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE llm_usage_logs (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, llm_connection_id INT NULL, user_id INT NOT NULL, purpose VARCHAR(120) NOT NULL, prompt_hash VARCHAR(255) NOT NULL, response_hash VARCHAR(255) NULL, token_count INT NOT NULL DEFAULT 0, status VARCHAR(50) NOT NULL, created_at DATETIME NOT NULL);
CREATE TABLE quotes (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, owner_user_id INT NOT NULL, quote_number VARCHAR(60) NOT NULL, client_id INT NOT NULL, job_id INT NULL, status VARCHAR(50) NOT NULL, subtotal DECIMAL(12,2) NOT NULL DEFAULT 0, tax DECIMAL(12,2) NOT NULL DEFAULT 0, total DECIMAL(12,2) NOT NULL DEFAULT 0, notes TEXT, valid_until DATE, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE quote_items (id INT AUTO_INCREMENT PRIMARY KEY, quote_id INT NOT NULL, description VARCHAR(255) NOT NULL, quantity DECIMAL(10,2) NOT NULL, unit_price DECIMAL(12,2) NOT NULL, line_total DECIMAL(12,2) NOT NULL);
CREATE TABLE invoices (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, owner_user_id INT NOT NULL, invoice_number VARCHAR(60) NOT NULL, client_id INT NOT NULL, job_id INT NULL, quote_id INT NULL, status VARCHAR(50) NOT NULL, subtotal DECIMAL(12,2) NOT NULL DEFAULT 0, tax DECIMAL(12,2) NOT NULL DEFAULT 0, total DECIMAL(12,2) NOT NULL DEFAULT 0, amount_paid DECIMAL(12,2) NOT NULL DEFAULT 0, due_date DATE, paid_at DATETIME NULL, payment_method VARCHAR(80) NULL, payment_reference VARCHAR(120) NULL, notes TEXT, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE invoice_items (id INT AUTO_INCREMENT PRIMARY KEY, invoice_id INT NOT NULL, description VARCHAR(255) NOT NULL, quantity DECIMAL(10,2) NOT NULL, unit_price DECIMAL(12,2) NOT NULL, line_total DECIMAL(12,2) NOT NULL);
CREATE TABLE settings (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, setting_key VARCHAR(120) NOT NULL, setting_value TEXT, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE api_tokens (id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, token_hash VARCHAR(255) NOT NULL, name VARCHAR(120) NOT NULL, revoked_at DATETIME NULL, created_at DATETIME NOT NULL);
CREATE TABLE audit_logs (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, user_id INT NULL, action VARCHAR(120) NOT NULL, entity_type VARCHAR(120), entity_id INT NULL, metadata JSON, created_at DATETIME NOT NULL);
CREATE TABLE import_jobs (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, user_id INT NOT NULL, type VARCHAR(50) NOT NULL, status VARCHAR(50) NOT NULL, error_report JSON, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE import_mapping_profiles (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, import_type VARCHAR(80) NOT NULL, name VARCHAR(190) NOT NULL, header_map JSON NOT NULL, required_fields JSON NOT NULL, created_by_user_id INT NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE import_validation_reports (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, import_job_id INT NULL, import_type VARCHAR(80) NOT NULL, status VARCHAR(50) NOT NULL, source_filename VARCHAR(255) NULL, file_mime_type VARCHAR(120) NULL, file_size_bytes INT NULL, row_count INT NOT NULL DEFAULT 0, missing_required JSON, warnings JSON, row_error_count INT NOT NULL DEFAULT 0, idempotency_key VARCHAR(190) NOT NULL, created_at DATETIME NOT NULL);
CREATE TABLE export_jobs (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, user_id INT NOT NULL, type VARCHAR(50) NOT NULL, status VARCHAR(50) NOT NULL, file_name VARCHAR(255), created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE demo_data_registry (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, table_name VARCHAR(120) NOT NULL, record_id INT NOT NULL, is_demo TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL);