-- Clients module: company records, project links, portal user sync

CREATE TABLE IF NOT EXISTS clients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_name VARCHAR(200) NOT NULL,
  slug VARCHAR(220) NOT NULL,
  contact_name VARCHAR(200) NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone VARCHAR(50) NULL,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  internal_notes TEXT NULL,
  user_id INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  UNIQUE KEY uq_clients_email (email, deleted_at),
  UNIQUE KEY uq_clients_slug (slug, deleted_at),
  CONSTRAINT fk_clients_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS client_projects (
  client_id INT NOT NULL,
  project_id INT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (client_id, project_id),
  CONSTRAINT fk_cp_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  CONSTRAINT fk_cp_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  INDEX idx_cp_project (project_id)
);

CREATE INDEX idx_clients_status ON clients (status, deleted_at);
CREATE INDEX idx_clients_company ON clients (company_name, deleted_at);

-- Align with existing portal seed user (002_client_user.ts uses client_id = 1)
INSERT INTO clients (id, company_name, slug, contact_name, email, phone, status, internal_notes)
SELECT 1, 'Acme Corp', 'acme-corp', 'Acme Contact', 'client1@acme.com', NULL, 'active', 'Seeded portal demo client'
WHERE NOT EXISTS (SELECT 1 FROM clients WHERE id = 1);

UPDATE users SET client_id = 1 WHERE email = 'client1@acme.com' AND client_id IS NULL;

UPDATE clients c
JOIN users u ON u.email = c.email AND u.deleted_at IS NULL
SET c.user_id = u.id
WHERE c.user_id IS NULL;