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 inventory_movements (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, job_id INT NOT NULL, inventory_item_id INT NOT NULL, assigned_user_id INT NOT NULL, quantity DECIMAL(10,2) NOT NULL, unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0, total_cost DECIMAL(12,2) NOT NULL DEFAULT 0, notes TEXT NULL, created_at DATETIME NOT NULL);
CREATE TABLE job_cost_entries (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, job_id INT NOT NULL, cost_type VARCHAR(40) NOT NULL, description VARCHAR(190) NOT NULL, quantity DECIMAL(10,2) NOT NULL DEFAULT 1, unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0, total_cost DECIMAL(12,2) NOT NULL DEFAULT 0, cost_date DATE NOT NULL, source VARCHAR(80) NOT NULL DEFAULT 'manual', notes TEXT NULL, created_by_user_id INT NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE timesheet_entries (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, owner_user_id INT NOT NULL, job_id INT NOT NULL, contract_id INT NULL, employee_name VARCHAR(190) NOT NULL, employee_email VARCHAR(190) NULL, work_date DATE NOT NULL, start_time TIME NULL, end_time TIME NULL, hours DECIMAL(8,2) NOT NULL DEFAULT 0, hourly_rate DECIMAL(12,2) NOT NULL DEFAULT 0, total_cost DECIMAL(12,2) NOT NULL DEFAULT 0, status VARCHAR(50) NOT NULL DEFAULT 'approved', source VARCHAR(80) NOT NULL DEFAULT 'payroll_import', external_reference VARCHAR(120) NULL, notes TEXT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE purchase_orders (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, owner_user_id INT NOT NULL, purchase_order_number VARCHAR(80) NOT NULL, supplier_name VARCHAR(190) NOT NULL, job_id INT NULL, contract_id INT NULL, status VARCHAR(50) NOT NULL DEFAULT 'draft', ordered_at DATE NULL, expected_at DATE NULL, received_at DATE NULL, currency VARCHAR(3) NOT NULL DEFAULT 'GBP', 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 NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE purchase_order_lines (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, purchase_order_id INT NOT NULL, inventory_item_id INT NULL, description VARCHAR(190) NOT NULL, quantity DECIMAL(10,2) NOT NULL DEFAULT 1, unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0, line_total DECIMAL(12,2) NOT NULL DEFAULT 0, received_quantity DECIMAL(10,2) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE supplier_invoices (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, owner_user_id INT NOT NULL, supplier_invoice_number VARCHAR(80) NOT NULL, supplier_name VARCHAR(190) NOT NULL, purchase_order_id INT NULL, purchase_order_number VARCHAR(80) NULL, job_id INT NULL, contract_id INT NULL, status VARCHAR(50) NOT NULL DEFAULT 'draft', match_status VARCHAR(50) NOT NULL DEFAULT 'unmatched', invoice_date DATE NULL, due_date DATE NULL, posted_at DATE NULL, currency VARCHAR(3) NOT NULL DEFAULT 'GBP', 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 NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE supplier_invoice_lines (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, supplier_invoice_id INT NOT NULL, purchase_order_line_id INT NULL, inventory_item_id INT NULL, description VARCHAR(190) NOT NULL, quantity DECIMAL(10,2) NOT NULL DEFAULT 1, unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0, line_total DECIMAL(12,2) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, 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, billing_schedule_accepted_at DATETIME NULL, 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, billing_point_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 billing_schedules (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, job_id INT NULL, quote_id INT NULL, contract_id INT NULL, client_id INT NOT NULL, name VARCHAR(190) NOT NULL, schedule_type VARCHAR(40) NOT NULL, contract_total DECIMAL(12,2) NOT NULL DEFAULT 0, currency VARCHAR(3) NOT NULL DEFAULT 'GBP', status VARCHAR(50) NOT NULL DEFAULT 'draft', created_by INT NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE billing_points (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, billing_schedule_id INT NOT NULL, sequence_number INT NOT NULL, name VARCHAR(190) NOT NULL, description TEXT NULL, trigger_type VARCHAR(40) NOT NULL, trigger_value VARCHAR(190) NULL, billing_method VARCHAR(40) NOT NULL, percentage DECIMAL(7,4) NULL, amount DECIMAL(12,2) NULL, due_offset_days INT NULL, invoice_id INT NULL, status VARCHAR(50) NOT NULL DEFAULT 'pending', ready_at DATETIME NULL, invoiced_at DATETIME NULL, paid_at DATETIME NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE payment_schedule_templates (id INT AUTO_INCREMENT PRIMARY KEY, tenant_id INT NOT NULL, name VARCHAR(190) NOT NULL, description TEXT NULL, is_default TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
CREATE TABLE payment_schedule_template_points (id INT AUTO_INCREMENT PRIMARY KEY, template_id INT NOT NULL, sequence_number INT NOT NULL, name VARCHAR(190) NOT NULL, trigger_type VARCHAR(40) NOT NULL, billing_method VARCHAR(40) NOT NULL, percentage DECIMAL(7,4) NULL, amount DECIMAL(12,2) NULL, due_offset_days INT 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);
CREATE INDEX idx_users_tenant_role_status ON users (tenant_id, role, status);
CREATE INDEX idx_clients_tenant_created ON clients (tenant_id, created_at);
CREATE INDEX idx_clients_tenant_name ON clients (tenant_id, name);
CREATE INDEX idx_jobs_tenant_status_schedule ON jobs (tenant_id, status, scheduled_date, scheduled_time);
CREATE INDEX idx_jobs_tenant_schedule ON jobs (tenant_id, scheduled_date, scheduled_time);
CREATE INDEX idx_jobs_tenant_client ON jobs (tenant_id, client_id);
CREATE INDEX idx_quotes_tenant_status_created ON quotes (tenant_id, status, created_at);
CREATE INDEX idx_quotes_tenant_number ON quotes (tenant_id, quote_number);
CREATE INDEX idx_quotes_tenant_client_created ON quotes (tenant_id, client_id, created_at);
CREATE INDEX idx_quote_items_quote ON quote_items (quote_id);
CREATE INDEX idx_invoices_tenant_status_due ON invoices (tenant_id, status, due_date);
CREATE INDEX idx_invoices_tenant_created ON invoices (tenant_id, created_at);
CREATE INDEX idx_invoices_tenant_number ON invoices (tenant_id, invoice_number);
CREATE INDEX idx_invoices_tenant_client_created ON invoices (tenant_id, client_id, created_at);
CREATE INDEX idx_invoice_items_invoice ON invoice_items (invoice_id);
CREATE INDEX idx_inventory_movements_tenant_job_created ON inventory_movements (tenant_id, job_id, created_at);
CREATE INDEX idx_job_cost_entries_tenant_job_date ON job_cost_entries (tenant_id, job_id, cost_date);
CREATE INDEX idx_job_cost_entries_tenant_type_date ON job_cost_entries (tenant_id, cost_type, cost_date);
CREATE INDEX idx_timesheet_entries_tenant_job_date ON timesheet_entries (tenant_id, job_id, work_date);
CREATE INDEX idx_timesheet_entries_tenant_contract_date ON timesheet_entries (tenant_id, contract_id, work_date);
CREATE INDEX idx_timesheet_entries_tenant_status_date ON timesheet_entries (tenant_id, status, work_date);
CREATE INDEX idx_timesheet_entries_tenant_employee_date ON timesheet_entries (tenant_id, employee_email, work_date);
CREATE INDEX idx_purchase_orders_tenant_job_ordered ON purchase_orders (tenant_id, job_id, ordered_at);
CREATE INDEX idx_purchase_orders_tenant_contract_ordered ON purchase_orders (tenant_id, contract_id, ordered_at);
CREATE INDEX idx_purchase_orders_tenant_status_ordered ON purchase_orders (tenant_id, status, ordered_at);
CREATE INDEX idx_purchase_order_lines_tenant_po ON purchase_order_lines (tenant_id, purchase_order_id);
CREATE INDEX idx_supplier_invoices_tenant_po_invoice ON supplier_invoices (tenant_id, purchase_order_id, invoice_date);
CREATE INDEX idx_supplier_invoices_tenant_job_invoice ON supplier_invoices (tenant_id, job_id, invoice_date);
CREATE INDEX idx_supplier_invoices_tenant_contract_invoice ON supplier_invoices (tenant_id, contract_id, invoice_date);
CREATE INDEX idx_supplier_invoices_tenant_status_invoice ON supplier_invoices (tenant_id, status, invoice_date);
CREATE INDEX idx_supplier_invoice_lines_tenant_invoice ON supplier_invoice_lines (tenant_id, supplier_invoice_id);
CREATE INDEX idx_import_jobs_tenant_created ON import_jobs (tenant_id, created_at);
CREATE INDEX idx_import_jobs_tenant_status_created ON import_jobs (tenant_id, status, created_at);
CREATE INDEX idx_import_validation_tenant_created ON import_validation_reports (tenant_id, created_at);
CREATE INDEX idx_import_validation_tenant_idempotency ON import_validation_reports (tenant_id, idempotency_key);
CREATE INDEX idx_export_jobs_tenant_created ON export_jobs (tenant_id, created_at);
CREATE INDEX idx_export_jobs_tenant_status_created ON export_jobs (tenant_id, status, created_at);
CREATE INDEX idx_report_delivery_jobs_tenant_status_scheduled ON report_delivery_jobs (tenant_id, status, scheduled_for);
CREATE INDEX idx_report_delivery_jobs_saved_report ON report_delivery_jobs (tenant_id, saved_report_id, scheduled_for);