SET FOREIGN_KEY_CHECKS=0; CREATE TABLE IF NOT EXISTS companies ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(160) NOT NULL, email VARCHAR(160) NULL, phone VARCHAR(60) NULL, address TEXT NULL, logo VARCHAR(255) NULL, letterhead_pdf_path VARCHAR(255) NULL, stamp_image_path VARCHAR(255) NULL, tax_number VARCHAR(100) NULL, currency VARCHAR(10) NOT NULL DEFAULT 'BWP', quotation_prefix VARCHAR(20) NOT NULL DEFAULT 'QT', invoice_prefix VARCHAR(20) NOT NULL DEFAULT 'INV', status ENUM('trial','active','suspended','cancelled') NOT NULL DEFAULT 'trial', created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, INDEX idx_companies_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, name VARCHAR(160) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, role ENUM('super_admin','owner','admin','sales','accountant','marketing','viewer') NOT NULL DEFAULT 'owner', status ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active', last_login_at TIMESTAMP NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_users_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, INDEX idx_users_company_role (company_id, role) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS subscription_plans ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, code VARCHAR(50) NOT NULL UNIQUE, monthly_price DECIMAL(12,2) NOT NULL DEFAULT 0, annual_price DECIMAL(12,2) NOT NULL DEFAULT 0, max_users INT NOT NULL DEFAULT 1, max_quotes_per_month INT NOT NULL DEFAULT 25, max_invoices_per_month INT NOT NULL DEFAULT 25, ai_posts_per_month INT NOT NULL DEFAULT 10, ai_text_generations_per_month INT NOT NULL DEFAULT 10, ai_images_per_month INT NOT NULL DEFAULT 0, ai_video_seconds_per_month INT NOT NULL DEFAULT 0, features JSON NULL, status ENUM('active','inactive') NOT NULL DEFAULT 'active', created_at TIMESTAMP NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS company_subscriptions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, plan_id BIGINT UNSIGNED NOT NULL, billing_cycle ENUM('monthly','annual') NOT NULL DEFAULT 'monthly', status ENUM('trial','active','past_due','cancelled','expired') NOT NULL DEFAULT 'trial', starts_at DATE NOT NULL, ends_at DATE NULL, next_billing_at DATE NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_sub_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_sub_plan FOREIGN KEY (plan_id) REFERENCES subscription_plans(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS subscription_payment_attempts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, company_subscription_id BIGINT UNSIGNED NOT NULL, amount DECIMAL(12,2) NOT NULL, currency VARCHAR(10) NOT NULL DEFAULT 'USD', gateway VARCHAR(40) NOT NULL DEFAULT 'dpo', reference VARCHAR(160) NOT NULL, transaction_token VARCHAR(190) NULL, status ENUM('pending','successful','failed','cancelled') NOT NULL DEFAULT 'pending', raw_response MEDIUMTEXT NULL, completed_at DATETIME NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_sub_pay_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_sub_pay_subscription FOREIGN KEY (company_subscription_id) REFERENCES company_subscriptions(id) ON DELETE CASCADE, INDEX idx_sub_pay_company_status (company_id, status, created_at), INDEX idx_sub_pay_token (transaction_token) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS usage_events ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NULL, resource VARCHAR(60) NOT NULL, quantity INT NOT NULL DEFAULT 1, source_type VARCHAR(80) NULL, source_id BIGINT UNSIGNED NULL, metadata JSON NULL, created_at TIMESTAMP NULL, CONSTRAINT fk_usage_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_usage_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_usage_company_resource_month (company_id, resource, created_at), INDEX idx_usage_user_resource_month (company_id, user_id, resource, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS clients ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, name VARCHAR(160) NOT NULL, company_name VARCHAR(160) NULL, email VARCHAR(190) NULL, phone VARCHAR(80) NULL, address TEXT NULL, tax_number VARCHAR(100) NULL, notes TEXT NULL, status ENUM('active','inactive') NOT NULL DEFAULT 'active', created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_clients_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, INDEX idx_clients_company_name (company_id, name), INDEX idx_clients_email (email) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS products ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, name VARCHAR(180) NOT NULL, sku VARCHAR(80) NULL, category VARCHAR(100) NULL, description TEXT NULL, image_path VARCHAR(255) NULL, unit_price DECIMAL(12,2) NOT NULL DEFAULT 0, cost_price DECIMAL(12,2) NOT NULL DEFAULT 0, tax_rate DECIMAL(5,2) NOT NULL DEFAULT 0, status ENUM('active','inactive') NOT NULL DEFAULT 'active', created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_products_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, INDEX idx_products_company_name (company_id, name), INDEX idx_products_sku (company_id, sku) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS vendors ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, name VARCHAR(180) NOT NULL, contact_name VARCHAR(160) NULL, email VARCHAR(190) NULL, phone VARCHAR(80) NULL, address TEXT NULL, tax_number VARCHAR(100) NULL, notes TEXT NULL, status ENUM('active','inactive') NOT NULL DEFAULT 'active', created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_vendors_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, INDEX idx_vendors_company_name (company_id, name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS document_counters ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, counter_key VARCHAR(60) NOT NULL, next_number BIGINT UNSIGNED NOT NULL DEFAULT 1, UNIQUE KEY uq_document_counter (company_id, counter_key), CONSTRAINT fk_counter_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS quotations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, client_id BIGINT UNSIGNED NOT NULL, quote_number VARCHAR(60) NOT NULL, revision_number INT NOT NULL DEFAULT 1, title VARCHAR(180) NOT NULL DEFAULT 'Quotation', issue_date DATE NOT NULL, expiry_date DATE NULL, subtotal DECIMAL(12,2) NOT NULL DEFAULT 0, tax_total DECIMAL(12,2) NOT NULL DEFAULT 0, discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0, deposit_amount DECIMAL(12,2) NOT NULL DEFAULT 0, total DECIMAL(12,2) NOT NULL DEFAULT 0, status ENUM('draft','sent','viewed','accepted','rejected','expired','converted') NOT NULL DEFAULT 'draft', approval_status ENUM('not_required','pending','approved','rejected') NOT NULL DEFAULT 'not_required', approval_reason VARCHAR(255) NULL, notes TEXT NULL, terms TEXT NULL, public_token VARCHAR(120) NOT NULL UNIQUE, sent_at TIMESTAMP NULL, viewed_at TIMESTAMP NULL, accepted_at TIMESTAMP NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_quotes_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_quotes_client FOREIGN KEY (client_id) REFERENCES clients(id), UNIQUE KEY uq_quote_number (company_id, quote_number), INDEX idx_quotes_company_status (company_id, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS quotation_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, quotation_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NULL, description TEXT NOT NULL, quantity DECIMAL(12,2) NOT NULL DEFAULT 1, unit_price DECIMAL(12,2) NOT NULL DEFAULT 0, tax_rate DECIMAL(5,2) NOT NULL DEFAULT 0, tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0, line_total DECIMAL(12,2) NOT NULL DEFAULT 0, created_at TIMESTAMP NULL, CONSTRAINT fk_quote_items_quote FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE CASCADE, CONSTRAINT fk_quote_items_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS quote_package_options ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, quotation_id BIGINT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL, description TEXT NULL, amount DECIMAL(12,2) NOT NULL DEFAULT 0, is_recommended TINYINT(1) NOT NULL DEFAULT 0, sort_order INT NOT NULL DEFAULT 0, created_at TIMESTAMP NULL, CONSTRAINT fk_quote_packages_quote FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE CASCADE, INDEX idx_quote_packages_quote (quotation_id, sort_order) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS quotation_revisions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, quotation_id BIGINT UNSIGNED NOT NULL, revision_number INT NOT NULL DEFAULT 1, summary VARCHAR(255) NOT NULL, snapshot_json JSON NULL, created_by VARCHAR(160) NULL, created_at TIMESTAMP NULL, CONSTRAINT fk_quote_revisions_quote FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE CASCADE, INDEX idx_quote_revisions_quote (quotation_id, revision_number) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS quotation_comments ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, quotation_id BIGINT UNSIGNED NOT NULL, author_type ENUM('client','user') NOT NULL, author_name VARCHAR(160) NOT NULL, author_email VARCHAR(190) NULL, comment TEXT NOT NULL, created_at TIMESTAMP NULL, CONSTRAINT fk_quote_comments_quote FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE CASCADE, INDEX idx_quote_comments_quote (quotation_id, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS quotation_acceptances ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, quotation_id BIGINT UNSIGNED NOT NULL, selected_package_option_id BIGINT UNSIGNED NULL, signer_name VARCHAR(160) NOT NULL, signer_email VARCHAR(190) NULL, signature_text VARCHAR(190) NOT NULL, accepted_terms TINYINT(1) NOT NULL DEFAULT 1, ip_address VARCHAR(80) NULL, user_agent VARCHAR(255) NULL, created_at TIMESTAMP NULL, CONSTRAINT fk_quote_acceptances_quote FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE CASCADE, CONSTRAINT fk_quote_acceptances_package FOREIGN KEY (selected_package_option_id) REFERENCES quote_package_options(id) ON DELETE SET NULL, INDEX idx_quote_acceptances_quote (quotation_id, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS invoices ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, quotation_id BIGINT UNSIGNED NULL, client_id BIGINT UNSIGNED NOT NULL, invoice_number VARCHAR(60) NOT NULL, title VARCHAR(180) NOT NULL DEFAULT 'Invoice', issue_date DATE NOT NULL, due_date DATE NULL, subtotal DECIMAL(12,2) NOT NULL DEFAULT 0, tax_total DECIMAL(12,2) NOT NULL DEFAULT 0, discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0, deposit_amount DECIMAL(12,2) NOT NULL DEFAULT 0, total DECIMAL(12,2) NOT NULL DEFAULT 0, paid_amount DECIMAL(12,2) NOT NULL DEFAULT 0, balance_due DECIMAL(12,2) NOT NULL DEFAULT 0, payment_request_amount DECIMAL(12,2) NOT NULL DEFAULT 0, status ENUM('draft','sent','viewed','partially_paid','paid','overdue','cancelled','refunded') NOT NULL DEFAULT 'draft', notes TEXT NULL, terms TEXT NULL, public_token VARCHAR(120) NOT NULL UNIQUE, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_invoices_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_invoices_client FOREIGN KEY (client_id) REFERENCES clients(id), CONSTRAINT fk_invoices_quote FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE SET NULL, UNIQUE KEY uq_invoice_number (company_id, invoice_number), INDEX idx_invoices_company_status (company_id, status), INDEX idx_invoices_due (due_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS recurring_invoice_profiles ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, client_id BIGINT UNSIGNED NOT NULL, source_invoice_id BIGINT UNSIGNED NOT NULL, name VARCHAR(180) NOT NULL, frequency ENUM('weekly','monthly','quarterly','annual') NOT NULL DEFAULT 'monthly', next_run_date DATE NOT NULL, last_run_date DATE NULL, end_date DATE NULL, auto_send TINYINT(1) NOT NULL DEFAULT 0, status ENUM('active','paused','cancelled','completed') NOT NULL DEFAULT 'active', created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_recurring_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_recurring_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE, CONSTRAINT fk_recurring_source_invoice FOREIGN KEY (source_invoice_id) REFERENCES invoices(id) ON DELETE CASCADE, INDEX idx_recurring_company_next (company_id, status, next_run_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS invoice_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invoice_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NULL, description TEXT NOT NULL, quantity DECIMAL(12,2) NOT NULL DEFAULT 1, unit_price DECIMAL(12,2) NOT NULL DEFAULT 0, tax_rate DECIMAL(5,2) NOT NULL DEFAULT 0, tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0, line_total DECIMAL(12,2) NOT NULL DEFAULT 0, created_at TIMESTAMP NULL, CONSTRAINT fk_invoice_items_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE, CONSTRAINT fk_invoice_items_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS invoice_milestones ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invoice_id BIGINT UNSIGNED NOT NULL, title VARCHAR(160) NOT NULL, description TEXT NULL, percent DECIMAL(5,2) NOT NULL DEFAULT 0, amount DECIMAL(12,2) NOT NULL DEFAULT 0, due_date DATE NULL, status ENUM('pending','requested','paid','waived') NOT NULL DEFAULT 'pending', requested_at TIMESTAMP NULL, paid_at TIMESTAMP NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_invoice_milestones_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE, INDEX idx_invoice_milestones_invoice (invoice_id, status, due_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS payments ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, invoice_id BIGINT UNSIGNED NOT NULL, payment_proof_id BIGINT UNSIGNED NULL, amount DECIMAL(12,2) NOT NULL, method ENUM('cash','bank_transfer','card','mobile_money','dpo','paypal','manual') NOT NULL DEFAULT 'manual', reference VARCHAR(160) NULL, bank_branch_name VARCHAR(160) NULL, bank_location VARCHAR(160) NULL, mobile_money_provider VARCHAR(40) NULL, mobile_money_number VARCHAR(80) NULL, gateway VARCHAR(60) NULL, gateway_payload JSON NULL, status ENUM('pending','successful','failed','refunded') NOT NULL DEFAULT 'successful', paid_at DATETIME NOT NULL, created_at TIMESTAMP NULL, CONSTRAINT fk_payments_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_payments_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE, INDEX idx_payments_company_paid (company_id, paid_at), INDEX idx_payments_invoice (invoice_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS payment_proofs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, invoice_id BIGINT UNSIGNED NOT NULL, client_name VARCHAR(160) NOT NULL, client_email VARCHAR(190) NULL, amount DECIMAL(12,2) NOT NULL DEFAULT 0, reference VARCHAR(160) NULL, method ENUM('bank_transfer','cash','card','mobile_money','manual') NOT NULL DEFAULT 'bank_transfer', bank_branch_name VARCHAR(160) NULL, bank_location VARCHAR(160) NULL, mobile_money_provider VARCHAR(40) NULL, mobile_money_number VARCHAR(80) NULL, proof_path VARCHAR(255) NULL, note TEXT NULL, status ENUM('pending','matched','rejected') NOT NULL DEFAULT 'pending', reviewed_at TIMESTAMP NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_payment_proofs_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_payment_proofs_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE, INDEX idx_payment_proofs_company_status (company_id, status, created_at), INDEX idx_payment_proofs_invoice (invoice_id, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS payment_gateway_accounts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, provider ENUM('dpo','orange_money','myzaka','smega','bank_transfer','other') NOT NULL, display_name VARCHAR(120) NOT NULL, merchant_identifier VARCHAR(160) NULL, instructions TEXT NULL, status ENUM('active','inactive') NOT NULL DEFAULT 'active', sort_order INT NOT NULL DEFAULT 0, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_payment_gateway_accounts_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, INDEX idx_payment_gateway_accounts_company (company_id, status, sort_order) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS payment_webhook_events ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NULL, provider VARCHAR(60) NOT NULL, external_id VARCHAR(190) NULL, signature_valid TINYINT(1) NOT NULL DEFAULT 0, status ENUM('received','processed','ignored','failed') NOT NULL DEFAULT 'received', payload JSON NULL, error_message VARCHAR(255) NULL, processed_at DATETIME NULL, created_at TIMESTAMP NULL, CONSTRAINT fk_payment_webhook_events_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL, UNIQUE KEY uq_payment_webhook_provider_external (provider, external_id), INDEX idx_payment_webhook_events_status (status, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS expenses ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, vendor_id BIGINT UNSIGNED NULL, expense_number VARCHAR(60) NULL, category VARCHAR(100) NOT NULL DEFAULT 'General', description VARCHAR(255) NOT NULL, expense_date DATE NOT NULL, due_date DATE NULL, subtotal DECIMAL(12,2) NOT NULL DEFAULT 0, tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0, total DECIMAL(12,2) NOT NULL DEFAULT 0, paid_amount DECIMAL(12,2) NOT NULL DEFAULT 0, status ENUM('unpaid','partially_paid','paid','overdue','cancelled') NOT NULL DEFAULT 'unpaid', payment_method ENUM('cash','bank_transfer','card','mobile_money','manual') NOT NULL DEFAULT 'manual', reference VARCHAR(160) NULL, receipt_path VARCHAR(255) NULL, notes TEXT NULL, paid_at DATETIME NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_expenses_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_expenses_vendor FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE SET NULL, INDEX idx_expenses_company_date (company_id, expense_date), INDEX idx_expenses_company_status (company_id, status), INDEX idx_expenses_vendor (vendor_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS email_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, mailable_type VARCHAR(60) NOT NULL, mailable_id BIGINT UNSIGNED NOT NULL, recipient VARCHAR(190) NOT NULL, subject VARCHAR(255) NOT NULL, status ENUM('queued','sent','failed','opened','clicked') NOT NULL DEFAULT 'queued', error_message TEXT NULL, sent_at TIMESTAMP NULL, opened_at TIMESTAMP NULL, created_at TIMESTAMP NULL, INDEX idx_email_logs_company (company_id, mailable_type, mailable_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS marketing_campaigns ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NULL, name VARCHAR(180) NOT NULL, platform VARCHAR(60) NOT NULL, objective VARCHAR(120) NULL, media_brief TEXT NULL, generated_content JSON NULL, status ENUM('draft','scheduled','published','completed') NOT NULL DEFAULT 'draft', scheduled_at DATETIME NULL, published_at DATETIME NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_campaign_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_campaign_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS social_accounts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, platform ENUM('facebook','instagram','linkedin','x','tiktok') NOT NULL, account_name VARCHAR(180) NOT NULL, provider_account_id VARCHAR(190) NULL, access_token TEXT NULL, refresh_token TEXT NULL, scopes TEXT NULL, token_type VARCHAR(60) NULL, token_expires_at DATETIME NULL, raw_response JSON NULL, connected_at TIMESTAMP NULL, status ENUM('connected','expired','disabled') NOT NULL DEFAULT 'connected', created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_social_accounts_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS social_posts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, social_account_id BIGINT UNSIGNED NULL, campaign_id BIGINT UNSIGNED NULL, external_post_id VARCHAR(180) NULL, platform VARCHAR(60) NOT NULL, content TEXT NOT NULL, media_type ENUM('none','image','video','reel') NOT NULL DEFAULT 'none', media_url TEXT NULL, media_source VARCHAR(120) NULL, media_path VARCHAR(255) NULL, status ENUM('draft','scheduled','published','failed') NOT NULL DEFAULT 'draft', approval_status ENUM('draft','review','approved','rejected') NOT NULL DEFAULT 'draft', approval_note TEXT NULL, approval_requested_at TIMESTAMP NULL, approved_at TIMESTAMP NULL, recycle_interval_days INT NULL, recycled_from_post_id BIGINT UNSIGNED NULL, performance_views INT NOT NULL DEFAULT 0, performance_clicks INT NOT NULL DEFAULT 0, performance_leads INT NOT NULL DEFAULT 0, performance_quotes INT NOT NULL DEFAULT 0, scheduled_at DATETIME NULL, published_at DATETIME NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_social_posts_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_social_posts_account FOREIGN KEY (social_account_id) REFERENCES social_accounts(id) ON DELETE SET NULL, CONSTRAINT fk_social_posts_campaign FOREIGN KEY (campaign_id) REFERENCES marketing_campaigns(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS marketing_media_assets ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NULL, title VARCHAR(180) NOT NULL, media_type ENUM('image','video','reel','link') NOT NULL DEFAULT 'image', media_url TEXT NOT NULL, local_path VARCHAR(255) NULL, download_status ENUM('remote','downloaded','failed') NOT NULL DEFAULT 'remote', download_attempts INT UNSIGNED NOT NULL DEFAULT 0, last_download_error TEXT NULL, source VARCHAR(120) NULL, tags VARCHAR(255) NULL, quality_status ENUM('ready','warning','blocked') NOT NULL DEFAULT 'warning', quality_notes TEXT NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_media_assets_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_media_assets_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL, INDEX idx_media_assets_company (company_id, media_type) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS social_post_media ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, social_post_id BIGINT UNSIGNED NOT NULL, marketing_media_asset_id BIGINT UNSIGNED NULL, media_type ENUM('image','video','reel','link') NOT NULL DEFAULT 'image', media_url TEXT NOT NULL, media_path VARCHAR(255) NULL, media_source VARCHAR(120) NULL, sort_order INT NOT NULL DEFAULT 0, created_at TIMESTAMP NULL, CONSTRAINT fk_social_post_media_post FOREIGN KEY (social_post_id) REFERENCES social_posts(id) ON DELETE CASCADE, CONSTRAINT fk_social_post_media_asset FOREIGN KEY (marketing_media_asset_id) REFERENCES marketing_media_assets(id) ON DELETE SET NULL, INDEX idx_social_post_media_post (social_post_id, sort_order) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS marketing_activity_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, campaign_id BIGINT UNSIGNED NULL, social_post_id BIGINT UNSIGNED NULL, action VARCHAR(80) NOT NULL, description VARCHAR(255) NOT NULL, payload JSON NULL, created_at TIMESTAMP NULL, CONSTRAINT fk_marketing_activity_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_marketing_activity_campaign FOREIGN KEY (campaign_id) REFERENCES marketing_campaigns(id) ON DELETE SET NULL, CONSTRAINT fk_marketing_activity_post FOREIGN KEY (social_post_id) REFERENCES social_posts(id) ON DELETE SET NULL, INDEX idx_marketing_activity_company (company_id, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS social_post_metrics ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, social_post_id BIGINT UNSIGNED NOT NULL, impressions INT NOT NULL DEFAULT 0, reach INT NOT NULL DEFAULT 0, likes INT NOT NULL DEFAULT 0, comments INT NOT NULL DEFAULT 0, shares INT NOT NULL DEFAULT 0, clicks INT NOT NULL DEFAULT 0, engagement_rate DECIMAL(8,4) NOT NULL DEFAULT 0, captured_at DATETIME NOT NULL, CONSTRAINT fk_social_metrics_post FOREIGN KEY (social_post_id) REFERENCES social_posts(id) ON DELETE CASCADE, INDEX idx_social_metrics_post_date (social_post_id, captured_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS sales_pipeline_stages ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL, slug VARCHAR(100) NOT NULL, color VARCHAR(20) NOT NULL DEFAULT '#4f76a2', probability TINYINT UNSIGNED NOT NULL DEFAULT 0, position INT NOT NULL DEFAULT 0, stage_type ENUM('open','won','lost') NOT NULL DEFAULT 'open', created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_pipeline_stages_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, UNIQUE KEY uq_pipeline_stage_slug (company_id, slug), INDEX idx_pipeline_stages_company_position (company_id, position) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS leads ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, owner_user_id BIGINT UNSIGNED NULL, campaign_id BIGINT UNSIGNED NULL, social_post_id BIGINT UNSIGNED NULL, converted_client_id BIGINT UNSIGNED NULL, name VARCHAR(160) NOT NULL, company_name VARCHAR(160) NULL, email VARCHAR(190) NULL, phone VARCHAR(80) NULL, source VARCHAR(80) NOT NULL DEFAULT 'manual', source_detail VARCHAR(190) NULL, status ENUM('new','contacted','qualified','disqualified','converted') NOT NULL DEFAULT 'new', estimated_value DECIMAL(12,2) NOT NULL DEFAULT 0, notes TEXT NULL, external_id VARCHAR(190) NULL, attribution_json JSON NULL, first_contacted_at DATETIME NULL, qualified_at DATETIME NULL, converted_at DATETIME NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_leads_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_leads_owner FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_leads_campaign FOREIGN KEY (campaign_id) REFERENCES marketing_campaigns(id) ON DELETE SET NULL, CONSTRAINT fk_leads_social_post FOREIGN KEY (social_post_id) REFERENCES social_posts(id) ON DELETE SET NULL, CONSTRAINT fk_leads_client FOREIGN KEY (converted_client_id) REFERENCES clients(id) ON DELETE SET NULL, INDEX idx_leads_company_status (company_id, status, created_at), INDEX idx_leads_company_owner_status (company_id, owner_user_id, status), INDEX idx_leads_company_source (company_id, source), INDEX idx_leads_external (company_id, external_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS deals ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, lead_id BIGINT UNSIGNED NULL, client_id BIGINT UNSIGNED NOT NULL, stage_id BIGINT UNSIGNED NOT NULL, owner_user_id BIGINT UNSIGNED NULL, quotation_id BIGINT UNSIGNED NULL, title VARCHAR(180) NOT NULL, value DECIMAL(12,2) NOT NULL DEFAULT 0, probability TINYINT UNSIGNED NOT NULL DEFAULT 0, expected_close_date DATE NULL, next_action VARCHAR(255) NULL, next_action_at DATETIME NULL, lost_reason VARCHAR(255) NULL, won_at DATETIME NULL, lost_at DATETIME NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_deals_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_deals_lead FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE SET NULL, CONSTRAINT fk_deals_client FOREIGN KEY (client_id) REFERENCES clients(id), CONSTRAINT fk_deals_stage FOREIGN KEY (stage_id) REFERENCES sales_pipeline_stages(id), CONSTRAINT fk_deals_owner FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_deals_quote FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE SET NULL, INDEX idx_deals_company_stage (company_id, stage_id), INDEX idx_deals_company_owner_stage (company_id, owner_user_id, stage_id), INDEX idx_deals_company_close (company_id, expected_close_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS sales_tasks ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, assigned_user_id BIGINT UNSIGNED NULL, created_by_user_id BIGINT UNSIGNED NULL, completed_by_user_id BIGINT UNSIGNED NULL, lead_id BIGINT UNSIGNED NULL, deal_id BIGINT UNSIGNED NULL, client_id BIGINT UNSIGNED NULL, title VARCHAR(180) NOT NULL, description TEXT NULL, due_at DATETIME NULL, priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal', status ENUM('open','in_progress','completed','cancelled') NOT NULL DEFAULT 'open', assigned_at DATETIME NULL, started_at DATETIME NULL, completed_at DATETIME NULL, cancelled_at DATETIME NULL, completion_note TEXT NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_sales_tasks_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_sales_tasks_user FOREIGN KEY (assigned_user_id) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_sales_tasks_creator FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_sales_tasks_completer FOREIGN KEY (completed_by_user_id) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_sales_tasks_lead FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE, CONSTRAINT fk_sales_tasks_deal FOREIGN KEY (deal_id) REFERENCES deals(id) ON DELETE CASCADE, CONSTRAINT fk_sales_tasks_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE, INDEX idx_sales_tasks_company_status (company_id, status, due_at), INDEX idx_sales_tasks_assignee_status (company_id, assigned_user_id, status, due_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS deal_activities ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NULL, lead_id BIGINT UNSIGNED NULL, deal_id BIGINT UNSIGNED NULL, client_id BIGINT UNSIGNED NULL, quotation_id BIGINT UNSIGNED NULL, invoice_id BIGINT UNSIGNED NULL, activity_type VARCHAR(80) NOT NULL, subject VARCHAR(190) NOT NULL, description TEXT NULL, metadata JSON NULL, occurred_at DATETIME NOT NULL, created_at TIMESTAMP NULL, CONSTRAINT fk_deal_activities_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_deal_activities_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_deal_activities_lead FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE, CONSTRAINT fk_deal_activities_deal FOREIGN KEY (deal_id) REFERENCES deals(id) ON DELETE CASCADE, CONSTRAINT fk_deal_activities_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE, CONSTRAINT fk_deal_activities_quote FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE CASCADE, CONSTRAINT fk_deal_activities_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE, INDEX idx_deal_activities_company_date (company_id, occurred_at), INDEX idx_deal_activities_deal (deal_id, occurred_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS marketing_links ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, campaign_id BIGINT UNSIGNED NULL, social_post_id BIGINT UNSIGNED NULL, token VARCHAR(80) NOT NULL, label VARCHAR(160) NOT NULL, destination_type ENUM('lead_form','whatsapp','website') NOT NULL DEFAULT 'lead_form', destination_url TEXT NULL, default_source VARCHAR(80) NOT NULL DEFAULT 'campaign', click_count INT UNSIGNED NOT NULL DEFAULT 0, lead_count INT UNSIGNED NOT NULL DEFAULT 0, status ENUM('active','paused') NOT NULL DEFAULT 'active', created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_marketing_links_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_marketing_links_campaign FOREIGN KEY (campaign_id) REFERENCES marketing_campaigns(id) ON DELETE SET NULL, CONSTRAINT fk_marketing_links_social_post FOREIGN KEY (social_post_id) REFERENCES social_posts(id) ON DELETE SET NULL, UNIQUE KEY uq_marketing_link_token (token), INDEX idx_marketing_links_company (company_id, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS marketing_link_visits ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, marketing_link_id BIGINT UNSIGNED NOT NULL, lead_id BIGINT UNSIGNED NULL, referrer TEXT NULL, utm_source VARCHAR(120) NULL, utm_medium VARCHAR(120) NULL, utm_campaign VARCHAR(160) NULL, ip_hash VARCHAR(64) NULL, user_agent VARCHAR(255) NULL, visited_at DATETIME NOT NULL, converted_at DATETIME NULL, CONSTRAINT fk_marketing_link_visits_link FOREIGN KEY (marketing_link_id) REFERENCES marketing_links(id) ON DELETE CASCADE, CONSTRAINT fk_marketing_link_visits_lead FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE SET NULL, INDEX idx_marketing_link_visits_link_date (marketing_link_id, visited_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS workflow_rules ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, name VARCHAR(160) NOT NULL, trigger_event VARCHAR(100) NOT NULL, entity_type VARCHAR(80) NOT NULL, conditions_json JSON NULL, actions_json JSON NOT NULL, status ENUM('active','paused') NOT NULL DEFAULT 'active', run_count INT UNSIGNED NOT NULL DEFAULT 0, last_run_at DATETIME NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_workflow_rules_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, INDEX idx_workflow_rules_trigger (company_id, trigger_event, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS workflow_runs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, workflow_rule_id BIGINT UNSIGNED NOT NULL, entity_type VARCHAR(80) NOT NULL, entity_id BIGINT UNSIGNED NULL, status ENUM('running','completed','failed','skipped') NOT NULL DEFAULT 'running', input_json JSON NULL, output_json JSON NULL, error_message TEXT NULL, retry_count INT UNSIGNED NOT NULL DEFAULT 0, started_at DATETIME NOT NULL, completed_at DATETIME NULL, created_at TIMESTAMP NULL, CONSTRAINT fk_workflow_runs_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_workflow_runs_rule FOREIGN KEY (workflow_rule_id) REFERENCES workflow_rules(id) ON DELETE CASCADE, INDEX idx_workflow_runs_company_date (company_id, started_at), INDEX idx_workflow_runs_rule (workflow_rule_id, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS client_portal_accounts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, client_id BIGINT UNSIGNED NOT NULL, email VARCHAR(190) NOT NULL, status ENUM('active','disabled') NOT NULL DEFAULT 'active', magic_token_hash VARCHAR(64) NULL, magic_token_expires_at DATETIME NULL, last_login_at DATETIME NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_client_portal_accounts_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_client_portal_accounts_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE, UNIQUE KEY uq_client_portal_account (company_id, client_id, email), INDEX idx_client_portal_magic (magic_token_hash, magic_token_expires_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS client_requests ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, client_id BIGINT UNSIGNED NOT NULL, portal_account_id BIGINT UNSIGNED NULL, assigned_user_id BIGINT UNSIGNED NULL, subject VARCHAR(180) NOT NULL, description TEXT NULL, preferred_date DATE NULL, status ENUM('new','reviewing','scheduled','completed','cancelled') NOT NULL DEFAULT 'new', created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_client_requests_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_client_requests_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE, CONSTRAINT fk_client_requests_account FOREIGN KEY (portal_account_id) REFERENCES client_portal_accounts(id) ON DELETE SET NULL, CONSTRAINT fk_client_requests_user FOREIGN KEY (assigned_user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_client_requests_company_status (company_id, status, created_at), INDEX idx_client_requests_client (client_id, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS client_documents ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, client_id BIGINT UNSIGNED NOT NULL, portal_account_id BIGINT UNSIGNED NULL, title VARCHAR(180) NOT NULL, file_path VARCHAR(255) NOT NULL, original_name VARCHAR(255) NOT NULL, mime_type VARCHAR(120) NULL, file_size BIGINT UNSIGNED NOT NULL DEFAULT 0, visibility ENUM('client','internal') NOT NULL DEFAULT 'client', created_at TIMESTAMP NULL, CONSTRAINT fk_client_documents_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_client_documents_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE, CONSTRAINT fk_client_documents_account FOREIGN KEY (portal_account_id) REFERENCES client_portal_accounts(id) ON DELETE SET NULL, INDEX idx_client_documents_client (client_id, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS proposal_templates ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, name VARCHAR(160) NOT NULL, title VARCHAR(180) NOT NULL DEFAULT 'Proposal', introduction TEXT NULL, scope_template TEXT NULL, terms_template TEXT NULL, acceptance_text TEXT NULL, status ENUM('active','inactive') NOT NULL DEFAULT 'active', created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_proposal_templates_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, INDEX idx_proposal_templates_company (company_id, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS quotation_proposals ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, quotation_id BIGINT UNSIGNED NOT NULL, proposal_template_id BIGINT UNSIGNED NULL, introduction TEXT NULL, scope_text TEXT NULL, acceptance_text TEXT NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_quotation_proposals_quote FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE CASCADE, CONSTRAINT fk_quotation_proposals_template FOREIGN KEY (proposal_template_id) REFERENCES proposal_templates(id) ON DELETE SET NULL, UNIQUE KEY uq_quotation_proposal (quotation_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS signature_certificates ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, quotation_id BIGINT UNSIGNED NOT NULL, quotation_acceptance_id BIGINT UNSIGNED NOT NULL, certificate_token VARCHAR(100) NOT NULL, document_hash VARCHAR(64) NOT NULL, signer_name VARCHAR(160) NOT NULL, signer_email VARCHAR(190) NULL, signature_method ENUM('typed','drawn','uploaded') NOT NULL DEFAULT 'typed', signing_order INT NOT NULL DEFAULT 1, ip_address VARCHAR(80) NULL, user_agent VARCHAR(255) NULL, signed_payload JSON NULL, signed_at DATETIME NOT NULL, created_at TIMESTAMP NULL, CONSTRAINT fk_signature_certificates_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_signature_certificates_quote FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE CASCADE, CONSTRAINT fk_signature_certificates_acceptance FOREIGN KEY (quotation_acceptance_id) REFERENCES quotation_acceptances(id) ON DELETE CASCADE, UNIQUE KEY uq_signature_certificate_token (certificate_token), UNIQUE KEY uq_signature_acceptance (quotation_acceptance_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS projects ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, client_id BIGINT UNSIGNED NOT NULL, deal_id BIGINT UNSIGNED NULL, quotation_id BIGINT UNSIGNED NULL, manager_user_id BIGINT UNSIGNED NULL, name VARCHAR(180) NOT NULL, description TEXT NULL, status ENUM('planned','in_progress','on_hold','completed','cancelled') NOT NULL DEFAULT 'planned', billing_type ENUM('fixed','hourly','milestone') NOT NULL DEFAULT 'fixed', budget DECIMAL(12,2) NOT NULL DEFAULT 0, start_date DATE NULL, due_date DATE NULL, completed_at DATETIME NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_projects_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_projects_client FOREIGN KEY (client_id) REFERENCES clients(id), CONSTRAINT fk_projects_deal FOREIGN KEY (deal_id) REFERENCES deals(id) ON DELETE SET NULL, CONSTRAINT fk_projects_quote FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE SET NULL, CONSTRAINT fk_projects_manager FOREIGN KEY (manager_user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_projects_company_status (company_id, status), INDEX idx_projects_due (company_id, due_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS project_tasks ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, project_id BIGINT UNSIGNED NOT NULL, assigned_user_id BIGINT UNSIGNED NULL, title VARCHAR(180) NOT NULL, description TEXT NULL, status ENUM('todo','in_progress','blocked','completed') NOT NULL DEFAULT 'todo', priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal', due_date DATE NULL, estimated_minutes INT UNSIGNED NOT NULL DEFAULT 0, billable TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0, completed_at DATETIME NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_project_tasks_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE, CONSTRAINT fk_project_tasks_user FOREIGN KEY (assigned_user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_project_tasks_project_status (project_id, status, due_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS time_entries ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, project_id BIGINT UNSIGNED NOT NULL, project_task_id BIGINT UNSIGNED NULL, user_id BIGINT UNSIGNED NULL, invoice_id BIGINT UNSIGNED NULL, work_date DATE NOT NULL, duration_minutes INT UNSIGNED NOT NULL, hourly_rate DECIMAL(12,2) NOT NULL DEFAULT 0, description VARCHAR(255) NULL, billable TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP NULL, CONSTRAINT fk_time_entries_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_time_entries_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE, CONSTRAINT fk_time_entries_task FOREIGN KEY (project_task_id) REFERENCES project_tasks(id) ON DELETE SET NULL, CONSTRAINT fk_time_entries_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_time_entries_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL, INDEX idx_time_entries_project_date (project_id, work_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS project_costs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, project_id BIGINT UNSIGNED NOT NULL, vendor_id BIGINT UNSIGNED NULL, description VARCHAR(255) NOT NULL, category VARCHAR(100) NOT NULL DEFAULT 'Materials', amount DECIMAL(12,2) NOT NULL DEFAULT 0, cost_date DATE NOT NULL, billable TINYINT(1) NOT NULL DEFAULT 0, receipt_path VARCHAR(255) NULL, created_at TIMESTAMP NULL, CONSTRAINT fk_project_costs_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_project_costs_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE, CONSTRAINT fk_project_costs_vendor FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE SET NULL, INDEX idx_project_costs_project_date (project_id, cost_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS appointments ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, project_id BIGINT UNSIGNED NULL, client_id BIGINT UNSIGNED NOT NULL, assigned_user_id BIGINT UNSIGNED NULL, title VARCHAR(180) NOT NULL, location VARCHAR(255) NULL, notes TEXT NULL, starts_at DATETIME NOT NULL, ends_at DATETIME NULL, status ENUM('scheduled','confirmed','in_progress','completed','cancelled') NOT NULL DEFAULT 'scheduled', client_visible TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_appointments_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_appointments_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE, CONSTRAINT fk_appointments_client FOREIGN KEY (client_id) REFERENCES clients(id), CONSTRAINT fk_appointments_user FOREIGN KEY (assigned_user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_appointments_company_start (company_id, starts_at, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS audit_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NULL, user_id BIGINT UNSIGNED NULL, action VARCHAR(120) NOT NULL, entity_type VARCHAR(80) NULL, entity_id BIGINT UNSIGNED NULL, ip_address VARCHAR(80) NULL, user_agent VARCHAR(255) NULL, payload JSON NULL, created_at TIMESTAMP NULL, INDEX idx_audit_company (company_id, created_at), INDEX idx_audit_entity (entity_type, entity_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS api_keys ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NULL, name VARCHAR(120) NOT NULL, token_prefix VARCHAR(20) NOT NULL, token_hash CHAR(64) NOT NULL UNIQUE, scopes JSON NULL, last_used_at DATETIME NULL, expires_at DATETIME NULL, revoked_at DATETIME NULL, created_at TIMESTAMP NULL, CONSTRAINT fk_api_keys_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_api_keys_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_api_keys_company (company_id, revoked_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS webhook_subscriptions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL, endpoint_url VARCHAR(500) NOT NULL, signing_secret VARCHAR(190) NOT NULL, events JSON NULL, status ENUM('active','inactive') NOT NULL DEFAULT 'active', created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_webhook_subscriptions_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, INDEX idx_webhook_subscriptions_company (company_id, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS webhook_deliveries ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, subscription_id BIGINT UNSIGNED NOT NULL, event_name VARCHAR(120) NOT NULL, payload JSON NOT NULL, status ENUM('pending','delivered','retrying','failed') NOT NULL DEFAULT 'pending', attempt_count INT UNSIGNED NOT NULL DEFAULT 0, response_code INT NULL, response_body VARCHAR(1000) NULL, next_attempt_at DATETIME NULL, delivered_at DATETIME NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_webhook_deliveries_subscription FOREIGN KEY (subscription_id) REFERENCES webhook_subscriptions(id) ON DELETE CASCADE, INDEX idx_webhook_deliveries_queue (status, next_attempt_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS bank_transactions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, transaction_date DATE NOT NULL, description VARCHAR(255) NOT NULL, reference VARCHAR(190) NULL, amount DECIMAL(12,2) NOT NULL, transaction_type ENUM('credit','debit') NOT NULL, matched_payment_id BIGINT UNSIGNED NULL, matched_expense_id BIGINT UNSIGNED NULL, import_batch VARCHAR(80) NULL, status ENUM('unmatched','matched','ignored') NOT NULL DEFAULT 'unmatched', created_at TIMESTAMP NULL, CONSTRAINT fk_bank_transactions_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_bank_transactions_payment FOREIGN KEY (matched_payment_id) REFERENCES payments(id) ON DELETE SET NULL, CONSTRAINT fk_bank_transactions_expense FOREIGN KEY (matched_expense_id) REFERENCES expenses(id) ON DELETE SET NULL, INDEX idx_bank_transactions_company (company_id, status, transaction_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS branches ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, name VARCHAR(160) NOT NULL, code VARCHAR(30) NOT NULL, email VARCHAR(190) NULL, phone VARCHAR(80) NULL, address TEXT NULL, timezone VARCHAR(80) NOT NULL DEFAULT 'Africa/Gaborone', currency VARCHAR(10) NOT NULL DEFAULT 'BWP', locale VARCHAR(20) NOT NULL DEFAULT 'en_BW', status ENUM('active','inactive') NOT NULL DEFAULT 'active', created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_branches_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, UNIQUE KEY uq_branches_company_code (company_id, code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS inventory_warehouses ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, branch_id BIGINT UNSIGNED NULL, name VARCHAR(160) NOT NULL, code VARCHAR(30) NOT NULL, address TEXT NULL, status ENUM('active','inactive') NOT NULL DEFAULT 'active', created_at TIMESTAMP NULL, CONSTRAINT fk_inventory_warehouses_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_inventory_warehouses_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL, UNIQUE KEY uq_inventory_warehouse_code (company_id, code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS inventory_stock_levels ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, warehouse_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, quantity_on_hand DECIMAL(12,3) NOT NULL DEFAULT 0, quantity_reserved DECIMAL(12,3) NOT NULL DEFAULT 0, reorder_level DECIMAL(12,3) NOT NULL DEFAULT 0, updated_at TIMESTAMP NULL, CONSTRAINT fk_inventory_stock_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_inventory_stock_warehouse FOREIGN KEY (warehouse_id) REFERENCES inventory_warehouses(id) ON DELETE CASCADE, CONSTRAINT fk_inventory_stock_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE, UNIQUE KEY uq_inventory_stock (warehouse_id, product_id), INDEX idx_inventory_stock_company (company_id, product_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS inventory_movements ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, warehouse_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, movement_type ENUM('opening','purchase','sale','adjustment','transfer_in','transfer_out','return') NOT NULL, quantity DECIMAL(12,3) NOT NULL, unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0, reference_type VARCHAR(60) NULL, reference_id BIGINT UNSIGNED NULL, note VARCHAR(255) NULL, created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP NULL, CONSTRAINT fk_inventory_movements_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_inventory_movements_warehouse FOREIGN KEY (warehouse_id) REFERENCES inventory_warehouses(id), CONSTRAINT fk_inventory_movements_product FOREIGN KEY (product_id) REFERENCES products(id), CONSTRAINT fk_inventory_movements_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_inventory_movements_product (company_id, product_id, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS sales_orders ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, client_id BIGINT UNSIGNED NOT NULL, quotation_id BIGINT UNSIGNED NULL, order_number VARCHAR(60) NOT NULL, order_date DATE NOT NULL, status ENUM('draft','confirmed','fulfilled','cancelled') NOT NULL DEFAULT 'draft', total DECIMAL(12,2) NOT NULL DEFAULT 0, notes TEXT NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_sales_orders_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_sales_orders_client FOREIGN KEY (client_id) REFERENCES clients(id), CONSTRAINT fk_sales_orders_quote FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE SET NULL, UNIQUE KEY uq_sales_order_number (company_id, order_number) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS purchase_orders ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, vendor_id BIGINT UNSIGNED NOT NULL, warehouse_id BIGINT UNSIGNED NULL, order_number VARCHAR(60) NOT NULL, order_date DATE NOT NULL, expected_date DATE NULL, status ENUM('draft','ordered','part_received','received','cancelled') NOT NULL DEFAULT 'draft', total DECIMAL(12,2) NOT NULL DEFAULT 0, notes TEXT NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_purchase_orders_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_purchase_orders_vendor FOREIGN KEY (vendor_id) REFERENCES vendors(id), CONSTRAINT fk_purchase_orders_warehouse FOREIGN KEY (warehouse_id) REFERENCES inventory_warehouses(id) ON DELETE SET NULL, UNIQUE KEY uq_purchase_order_number (company_id, order_number) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS commerce_order_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, sales_order_id BIGINT UNSIGNED NULL, purchase_order_id BIGINT UNSIGNED NULL, product_id BIGINT UNSIGNED NULL, description VARCHAR(255) NOT NULL, quantity DECIMAL(12,3) NOT NULL DEFAULT 1, unit_price DECIMAL(12,2) NOT NULL DEFAULT 0, line_total DECIMAL(12,2) NOT NULL DEFAULT 0, created_at TIMESTAMP NULL, CONSTRAINT fk_commerce_items_sales_order FOREIGN KEY (sales_order_id) REFERENCES sales_orders(id) ON DELETE CASCADE, CONSTRAINT fk_commerce_items_purchase_order FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders(id) ON DELETE CASCADE, CONSTRAINT fk_commerce_items_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL, INDEX idx_commerce_items_sales_order (sales_order_id), INDEX idx_commerce_items_purchase_order (purchase_order_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS vendor_bills ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, vendor_id BIGINT UNSIGNED NOT NULL, purchase_order_id BIGINT UNSIGNED NULL, bill_number VARCHAR(80) NOT NULL, bill_date DATE NOT NULL, due_date DATE NULL, total DECIMAL(12,2) NOT NULL DEFAULT 0, paid_amount DECIMAL(12,2) NOT NULL DEFAULT 0, status ENUM('draft','open','part_paid','paid','void') NOT NULL DEFAULT 'open', notes TEXT NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT fk_vendor_bills_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_vendor_bills_vendor FOREIGN KEY (vendor_id) REFERENCES vendors(id), CONSTRAINT fk_vendor_bills_po FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders(id) ON DELETE SET NULL, INDEX idx_vendor_bills_company_due (company_id, status, due_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS guide_download_leads ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, email VARCHAR(190) NOT NULL, name VARCHAR(120) NULL, guide_slug VARCHAR(100) NOT NULL DEFAULT 'bizquote-pro-user-guide', download_token_hash CHAR(64) NOT NULL, email_status ENUM('pending','sent','failed') NOT NULL DEFAULT 'pending', email_error VARCHAR(500) NULL, email_sent_at DATETIME NULL, downloaded_at DATETIME NULL, download_count INT UNSIGNED NOT NULL DEFAULT 0, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, UNIQUE KEY uq_guide_download_token (download_token_hash), INDEX idx_guide_download_email (email, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS support_requests ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, ticket_number VARCHAR(32) NOT NULL, public_token_hash CHAR(64) NOT NULL, company_id BIGINT UNSIGNED NULL, user_id BIGINT UNSIGNED NULL, name VARCHAR(120) NOT NULL, email VARCHAR(190) NOT NULL, category VARCHAR(60) NOT NULL, subject VARCHAR(160) NOT NULL, message TEXT NOT NULL, priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal', status ENUM('new','open','in_progress','waiting','resolved','closed') NOT NULL DEFAULT 'new', source_page VARCHAR(255) NULL, support_email_status ENUM('pending','sent','failed') NOT NULL DEFAULT 'pending', requester_email_status ENUM('pending','sent','failed') NOT NULL DEFAULT 'pending', support_email_error VARCHAR(500) NULL, requester_email_error VARCHAR(500) NULL, resolved_at DATETIME NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, UNIQUE KEY uq_support_ticket_number (ticket_number), UNIQUE KEY uq_support_public_token (public_token_hash), INDEX idx_support_status_created (status, created_at), INDEX idx_support_company_created (company_id, created_at), INDEX idx_support_user_created (user_id, created_at), CONSTRAINT fk_support_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL, CONSTRAINT fk_support_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS support_request_messages ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, support_request_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NULL, sender_type ENUM('requester','staff','system') NOT NULL DEFAULT 'staff', sender_name VARCHAR(120) NOT NULL, sender_email VARCHAR(190) NULL, message TEXT NOT NULL, email_status ENUM('pending','sent','failed') NOT NULL DEFAULT 'pending', email_error VARCHAR(500) NULL, created_at TIMESTAMP NULL, INDEX idx_support_message_request (support_request_id, created_at), CONSTRAINT fk_support_message_request FOREIGN KEY (support_request_id) REFERENCES support_requests(id) ON DELETE CASCADE, CONSTRAINT fk_support_message_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT IGNORE INTO subscription_plans (id, name, code, monthly_price, annual_price, max_users, max_quotes_per_month, max_invoices_per_month, ai_posts_per_month, ai_text_generations_per_month, ai_images_per_month, ai_video_seconds_per_month, features, status, created_at) VALUES (1, 'Starter', 'starter', 19.00, 190.00, 2, 50, 50, 10, 10, 0, 0, JSON_OBJECT('tagline', 'Best for lean teams sending their first quotes.', 'badge', 'Starter', 'pdf', true, 'email', true, 'payments', false, 'ai_marketing', false, 'reports', false, 'advanced_reports', false, 'api', false, 'custom_branding', false), 'active', NOW()), (2, 'Professional', 'professional', 59.00, 590.00, 10, 500, 500, 100, 100, 20, 0, JSON_OBJECT('tagline', 'Best for growing teams using AI and payments.', 'badge', 'Recommended', 'pdf', true, 'email', true, 'payments', true, 'ai_marketing', true, 'reports', true, 'advanced_reports', false, 'api', false, 'custom_branding', false), 'active', NOW()), (3, 'Enterprise', 'enterprise', 199.00, 1990.00, 50, 5000, 5000, 1000, 1000, 100, 0, JSON_OBJECT('tagline', 'Best for larger teams needing scale and control.', 'badge', 'Scale', 'pdf', true, 'email', true, 'payments', true, 'ai_marketing', true, 'reports', true, 'advanced_reports', true, 'api', true, 'custom_branding', true), 'active', NOW()); SET FOREIGN_KEY_CHECKS=1;