CREATE TABLE IF NOT EXISTS companies (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(120) NOT NULL,
  name VARCHAR(190) NOT NULL,
  legal_name VARCHAR(190),
  email VARCHAR(190),
  phone VARCHAR(60),
  address TEXT,
  website_url VARCHAR(255),
  logo_path VARCHAR(255),
  primary_color VARCHAR(20) NOT NULL DEFAULT '#0b6b88',
  secondary_color VARCHAR(20) NOT NULL DEFAULT '#f3b23c',
  vat_enabled TINYINT(1) NOT NULL DEFAULT 0,
  vat_rate DECIMAL(5,2) NOT NULL DEFAULT 0,
  vat_label VARCHAR(190) NOT NULL DEFAULT 'TVA non applicable',
  iban VARCHAR(34),
  service_zones_json JSON NULL,
  public_site_enabled TINYINT(1) NOT NULL DEFAULT 1,
  status ENUM('active','suspended','archived') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_companies_slug (slug),
  INDEX idx_companies_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS company_domains (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL,
  hostname VARCHAR(190) NOT NULL,
  type ENUM('subdomain','custom') NOT NULL DEFAULT 'subdomain',
  status ENUM('pending','active','disabled') NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_company_domains_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  UNIQUE KEY uniq_company_domains_hostname (hostname),
  INDEX idx_company_domains_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO companies
  (slug, name, legal_name, email, phone, address, primary_color, secondary_color, vat_enabled, vat_rate, vat_label, status, created_at)
VALUES
  ('saas-demo', 'SaaS DEMO', 'SaaS DEMO Sàrl', 'demo@saas-demo.test', '079 000 00 00', 'Rue Démo 1\n1000 Lausanne', '#064b78', '#f5b84b', 0, 8.1, 'TVA non applicable', 'active', NOW())
ON DUPLICATE KEY UPDATE
  name=VALUES(name),
  legal_name=VALUES(legal_name),
  email=VALUES(email),
  phone=VALUES(phone),
  address=VALUES(address),
  updated_at=NOW();

SET @default_company_id = (SELECT id FROM companies WHERE slug='saas-demo' LIMIT 1);

ALTER TABLE users ADD COLUMN company_id INT UNSIGNED NULL AFTER id;
ALTER TABLE clients_profiles ADD COLUMN company_id INT UNSIGNED NULL AFTER user_id;
ALTER TABLE settings ADD COLUMN company_id INT UNSIGNED NULL FIRST;
ALTER TABLE bank_accounts ADD COLUMN company_id INT UNSIGNED NULL AFTER id;
ALTER TABLE quote_requests ADD COLUMN company_id INT UNSIGNED NULL AFTER id;
ALTER TABLE quote_request_files ADD COLUMN company_id INT UNSIGNED NULL AFTER id;
ALTER TABLE quotes ADD COLUMN company_id INT UNSIGNED NULL AFTER id;
ALTER TABLE quote_items ADD COLUMN company_id INT UNSIGNED NULL AFTER id;
ALTER TABLE quote_acceptances ADD COLUMN company_id INT UNSIGNED NULL AFTER id;
ALTER TABLE interventions ADD COLUMN company_id INT UNSIGNED NULL AFTER id;
ALTER TABLE intervention_staff ADD COLUMN company_id INT UNSIGNED NULL AFTER intervention_id;
ALTER TABLE invoices ADD COLUMN company_id INT UNSIGNED NULL AFTER id;
ALTER TABLE invoice_items ADD COLUMN company_id INT UNSIGNED NULL AFTER id;
ALTER TABLE payments ADD COLUMN company_id INT UNSIGNED NULL AFTER id;
ALTER TABLE messages_threads ADD COLUMN company_id INT UNSIGNED NULL AFTER id;
ALTER TABLE messages ADD COLUMN company_id INT UNSIGNED NULL AFTER id;
ALTER TABLE reviews ADD COLUMN company_id INT UNSIGNED NULL AFTER id;
ALTER TABLE site_photos ADD COLUMN company_id INT UNSIGNED NULL AFTER id;
ALTER TABLE blog_posts ADD COLUMN company_id INT UNSIGNED NULL AFTER id;
ALTER TABLE password_resets ADD COLUMN company_id INT UNSIGNED NULL AFTER id;
ALTER TABLE activity_logs ADD COLUMN company_id INT UNSIGNED NULL AFTER id;

UPDATE users SET company_id=@default_company_id WHERE company_id IS NULL;
UPDATE clients_profiles p JOIN users u ON u.id=p.user_id SET p.company_id=u.company_id WHERE p.company_id IS NULL;
UPDATE settings SET company_id=@default_company_id WHERE company_id IS NULL;
UPDATE bank_accounts SET company_id=@default_company_id WHERE company_id IS NULL;
UPDATE quote_requests SET company_id=@default_company_id WHERE company_id IS NULL;
UPDATE quote_request_files f JOIN quote_requests r ON r.id=f.quote_request_id SET f.company_id=r.company_id WHERE f.company_id IS NULL;
UPDATE quotes SET company_id=@default_company_id WHERE company_id IS NULL;
UPDATE quote_items qi JOIN quotes q ON q.id=qi.quote_id SET qi.company_id=q.company_id WHERE qi.company_id IS NULL;
UPDATE quote_acceptances qa JOIN quotes q ON q.id=qa.quote_id SET qa.company_id=q.company_id WHERE qa.company_id IS NULL;
UPDATE interventions SET company_id=@default_company_id WHERE company_id IS NULL;
UPDATE intervention_staff s JOIN interventions i ON i.id=s.intervention_id SET s.company_id=i.company_id WHERE s.company_id IS NULL;
UPDATE invoices SET company_id=@default_company_id WHERE company_id IS NULL;
UPDATE invoice_items ii JOIN invoices i ON i.id=ii.invoice_id SET ii.company_id=i.company_id WHERE ii.company_id IS NULL;
UPDATE payments p JOIN invoices i ON i.id=p.invoice_id SET p.company_id=i.company_id WHERE p.company_id IS NULL;
UPDATE messages_threads SET company_id=@default_company_id WHERE company_id IS NULL;
UPDATE messages m JOIN messages_threads t ON t.id=m.thread_id SET m.company_id=t.company_id WHERE m.company_id IS NULL;
UPDATE reviews r JOIN interventions i ON i.id=r.intervention_id SET r.company_id=i.company_id WHERE r.company_id IS NULL;
UPDATE site_photos SET company_id=@default_company_id WHERE company_id IS NULL;
UPDATE blog_posts SET company_id=@default_company_id WHERE company_id IS NULL;
UPDATE password_resets pr JOIN users u ON u.id=pr.user_id SET pr.company_id=u.company_id WHERE pr.company_id IS NULL;
UPDATE activity_logs l LEFT JOIN users u ON u.id=l.user_id SET l.company_id=COALESCE(u.company_id, @default_company_id) WHERE l.company_id IS NULL;

ALTER TABLE settings MODIFY company_id INT UNSIGNED NOT NULL;

ALTER TABLE users ADD INDEX idx_users_company (company_id);
ALTER TABLE clients_profiles ADD INDEX idx_clients_profiles_company (company_id);
ALTER TABLE settings DROP PRIMARY KEY, ADD PRIMARY KEY (company_id, name);
ALTER TABLE bank_accounts ADD INDEX idx_bank_accounts_company (company_id);
ALTER TABLE quote_requests ADD INDEX idx_quote_requests_company (company_id);
ALTER TABLE quote_request_files ADD INDEX idx_quote_request_files_company (company_id);
ALTER TABLE quotes ADD INDEX idx_quotes_company (company_id);
ALTER TABLE quote_items ADD INDEX idx_quote_items_company (company_id);
ALTER TABLE quote_acceptances ADD INDEX idx_quote_acceptances_company (company_id);
ALTER TABLE interventions ADD INDEX idx_interventions_company (company_id);
ALTER TABLE intervention_staff ADD INDEX idx_intervention_staff_company (company_id);
ALTER TABLE invoices ADD INDEX idx_invoices_company (company_id);
ALTER TABLE invoice_items ADD INDEX idx_invoice_items_company (company_id);
ALTER TABLE payments ADD INDEX idx_payments_company (company_id);
ALTER TABLE messages_threads ADD INDEX idx_messages_threads_company (company_id);
ALTER TABLE messages ADD INDEX idx_messages_company (company_id);
ALTER TABLE reviews ADD INDEX idx_reviews_company (company_id);
ALTER TABLE site_photos ADD INDEX idx_site_photos_company (company_id);
ALTER TABLE blog_posts ADD INDEX idx_blog_posts_company (company_id);
ALTER TABLE password_resets ADD INDEX idx_password_resets_company (company_id);
ALTER TABLE activity_logs ADD INDEX idx_activity_logs_company (company_id);

ALTER TABLE users ADD CONSTRAINT fk_users_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE RESTRICT;
ALTER TABLE settings ADD CONSTRAINT fk_settings_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE;