CREATE DATABASE IF NOT EXISTS ske_leads CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ske_leads;

CREATE TABLE roles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  description VARCHAR(255) NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB;

CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  username VARCHAR(80) NOT NULL UNIQUE,
  email VARCHAR(190) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  photo VARCHAR(255) NULL,
  role_id INT UNSIGNED NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  email_verified_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  CONSTRAINT fk_users_role FOREIGN KEY(role_id) REFERENCES roles(id)
) ENGINE=InnoDB;

CREATE TABLE customers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  company VARCHAR(190) NULL,
  phone VARCHAR(30) NOT NULL UNIQUE,
  email VARCHAR(190) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX idx_customers_company(company)
) ENGINE=InnoDB;

CREATE TABLE lead_sources (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  description VARCHAR(255) NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB;

CREATE TABLE lead_statuses (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL UNIQUE,
  color VARCHAR(20) NOT NULL DEFAULT '#000590',
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB;

CREATE TABLE leads (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  owner_id INT UNSIGNED NOT NULL,
  assigned_to INT UNSIGNED NULL,
  source_id INT UNSIGNED NULL,
  status_id INT UNSIGNED NOT NULL,
  nomor_sph VARCHAR(80) NULL,
  description VARCHAR(255) NULL,
  project_location VARCHAR(190) NULL,
  requirement TEXT NULL,
  notes TEXT NULL,
  created_by INT UNSIGNED NOT NULL,
  deleted_at DATETIME NULL,
  deleted_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  FOREIGN KEY(customer_id) REFERENCES customers(id),
  FOREIGN KEY(owner_id) REFERENCES users(id),
  FOREIGN KEY(assigned_to) REFERENCES users(id),
  FOREIGN KEY(source_id) REFERENCES lead_sources(id),
  FOREIGN KEY(status_id) REFERENCES lead_statuses(id),
  FOREIGN KEY(created_by) REFERENCES users(id),
  INDEX idx_leads_created(created_at),
  INDEX idx_leads_owner(owner_id),
  INDEX idx_leads_assigned(assigned_to),
  INDEX idx_leads_status(status_id)
) ENGINE=InnoDB;

CREATE TABLE lead_assignments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  lead_id BIGINT UNSIGNED NOT NULL,
  from_user_id INT UNSIGNED NULL,
  to_user_id INT UNSIGNED NOT NULL,
  assigned_by INT UNSIGNED NOT NULL,
  reason VARCHAR(255) NULL,
  assigned_at DATETIME NOT NULL,
  FOREIGN KEY(lead_id) REFERENCES leads(id),
  FOREIGN KEY(from_user_id) REFERENCES users(id),
  FOREIGN KEY(to_user_id) REFERENCES users(id),
  FOREIGN KEY(assigned_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE lead_activities (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  lead_id BIGINT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NULL,
  activity_type VARCHAR(80) NOT NULL,
  description TEXT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY(lead_id) REFERENCES leads(id),
  FOREIGN KEY(user_id) REFERENCES users(id),
  INDEX idx_activity_lead(lead_id)
) ENGINE=InnoDB;

CREATE TABLE quotations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  lead_id BIGINT UNSIGNED NOT NULL,
  quotation_number VARCHAR(100) NULL,
  quotation_date DATE NULL,
  quotation_value DECIMAL(15,2) NULL,
  negotiated_value DECIMAL(15,2) NULL,
  po_value DECIMAL(15,2) NULL,
  real_margin DECIMAL(15,2) NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'DRAFT',
  notes TEXT NULL,
  created_by INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  FOREIGN KEY(lead_id) REFERENCES leads(id),
  FOREIGN KEY(created_by) REFERENCES users(id),
  INDEX idx_quote_date(quotation_date),
  INDEX idx_quote_status(status)
) ENGINE=InnoDB;

CREATE TABLE closings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  lead_id BIGINT UNSIGNED NOT NULL,
  quotation_id BIGINT UNSIGNED NULL,
  po_number VARCHAR(100) NULL,
  po_date DATE NULL,
  po_value DECIMAL(15,2) NULL,
  real_margin DECIMAL(15,2) NULL,
  closing_date DATE NULL,
  notes TEXT NULL,
  created_by INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  FOREIGN KEY(lead_id) REFERENCES leads(id),
  FOREIGN KEY(quotation_id) REFERENCES quotations(id),
  FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  action VARCHAR(80) NOT NULL,
  table_name VARCHAR(80) NOT NULL,
  record_id BIGINT UNSIGNED NULL,
  old_data LONGTEXT NULL,
  new_data LONGTEXT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY(user_id) REFERENCES users(id),
  INDEX idx_audit_created(created_at)
) ENGINE=InnoDB;

CREATE TABLE import_batches (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  filename VARCHAR(255) NOT NULL,
  total_rows INT NOT NULL DEFAULT 0,
  success_rows INT NOT NULL DEFAULT 0,
  duplicate_rows INT NOT NULL DEFAULT 0,
  error_rows INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE password_reset_tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  token_hash VARCHAR(255) NOT NULL,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB;

INSERT INTO roles(name,description,created_at) VALUES
('superadmin','Akses penuh sistem',NOW()),
('sales','Akses data sales/marketing',NOW());

INSERT INTO lead_sources(name,description,created_at) VALUES
('Website Induk','Website utama SKE',NOW()),
('AntiPetir.net','Website antipetir.net',NOW()),
('Google Ads','Iklan Google',NOW()),
('Organic Google','Pencarian organik Google',NOW()),
('Referral','Rujukan pelanggan/rekanan',NOW()),
('WhatsApp Direct','WhatsApp langsung',NOW()),
('Lainnya','Sumber lain',NOW());

INSERT INTO lead_statuses(name,color,sort_order,created_at) VALUES
('Baru','#000590',1,NOW()),
('Proses','#2563eb',2,NOW()),
('Penawaran','#f59e0b',3,NOW()),
('Negosiasi','#f97316',4,NOW()),
('PO','#16a34a',5,NOW()),
('Selesai','#15803d',6,NOW()),
('Tidak Jadi','#dc2626',7,NOW());