-- BlockSpark Automotive DMS schema (MySQL)
-- DB: blockera_blockspark_dms

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','manager','sales','finance','ops','viewer') NOT NULL DEFAULT 'sales',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  type ENUM('particular','empresa') NOT NULL DEFAULT 'particular',
  name VARCHAR(160) NOT NULL,
  nif VARCHAR(30) NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(40) NULL,
  address VARCHAR(220) NULL,
  postal_code VARCHAR(20) NULL,
  city VARCHAR(80) NULL,
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS vehicles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  internal_code VARCHAR(30) NOT NULL UNIQUE,
  brand VARCHAR(60) NOT NULL,
  model VARCHAR(90) NOT NULL,
  version VARCHAR(120) NULL,
  plate VARCHAR(20) NULL,
  vin VARCHAR(25) NULL,
  fuel ENUM('gasolina','diesel','hibrido','phev','eletrico','gnv','outro') NOT NULL DEFAULT 'outro',
  year SMALLINT NULL,
  km INT NULL,
  color VARCHAR(60) NULL,
  source ENUM('autorola','retoma','particular','importacao','outro') NOT NULL DEFAULT 'outro',
  supplier_name VARCHAR(190) NULL,
  buy_date DATE NULL,
  entry_date DATE NOT NULL,
  ready_date DATE NULL,
  buy_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  sell_price_target DECIMAL(12,2) NOT NULL DEFAULT 0,
  status ENUM('disponivel','em_preparacao','reservado','vendido','anulado') NOT NULL DEFAULT 'disponivel',
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS vehicle_costs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  vehicle_id INT NOT NULL,
  cost_type ENUM('compra','transporte','recondicionamento','pecas','pintura','limpeza','garantia','publicidade','outsourcing','outros') NOT NULL,
  supplier VARCHAR(190) NULL,
  invoice_no VARCHAR(80) NULL,
  invoice_date DATE NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  vat_amount DECIMAL(12,2) NULL,
  notes TEXT NULL,
  created_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_cost_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE,
  CONSTRAINT fk_cost_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS documents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  vehicle_id INT NULL,
  customer_id INT NULL,
  doc_type ENUM(
    'fatura_compra','fatura_transporte','fatura_recondicionamento','fatura_pecas','fatura_pintura','fatura_limpeza',
    'fatura_venda','garantia','inspecao','financiamento','seguro','comprovativo_pagamento','livrete','manual','outro'
  ) NOT NULL DEFAULT 'outro',
  title VARCHAR(190) NOT NULL,
  original_filename VARCHAR(255) NOT NULL,
  stored_filename VARCHAR(255) NOT NULL,
  mime_type VARCHAR(120) NOT NULL,
  file_size INT NOT NULL,
  sha256 CHAR(64) NOT NULL,
  doc_date DATE NULL,
  invoice_no VARCHAR(80) NULL,
  supplier VARCHAR(190) NULL,
  created_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_doc_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE SET NULL,
  CONSTRAINT fk_doc_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
  CONSTRAINT fk_doc_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX (vehicle_id),
  INDEX (customer_id),
  INDEX (doc_type),
  INDEX (invoice_no)
);

CREATE TABLE IF NOT EXISTS vehicle_photos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  vehicle_id INT NOT NULL,
  title VARCHAR(190) NULL,
  original_filename VARCHAR(255) NOT NULL,
  stored_filename VARCHAR(255) NOT NULL,
  mime_type VARCHAR(120) NOT NULL,
  file_size INT NOT NULL,
  sha256 CHAR(64) NOT NULL,
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  created_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_photo_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE,
  CONSTRAINT fk_photo_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS leads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  source ENUM('standvirtual','olx','marketplace','google','referencia','outro') NOT NULL DEFAULT 'outro',
  customer_id INT NULL,
  name VARCHAR(160) NOT NULL,
  phone VARCHAR(40) NULL,
  email VARCHAR(190) NULL,
  vehicle_id INT NULL,
  stage ENUM('novo','contactado','test_drive','proposta','financiamento','fechado','perdido') NOT NULL DEFAULT 'novo',
  offer_amount DECIMAL(12,2) NULL,
  lost_reason VARCHAR(190) NULL,
  notes TEXT NULL,
  owner_user_id INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  CONSTRAINT fk_lead_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
  CONSTRAINT fk_lead_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE SET NULL,
  CONSTRAINT fk_lead_owner FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS sales (
  id INT AUTO_INCREMENT PRIMARY KEY,
  vehicle_id INT NOT NULL UNIQUE,
  customer_id INT NOT NULL,
  lead_id INT NULL,
  sale_date DATE NOT NULL,
  days_to_sell INT NULL,
  sale_price DECIMAL(12,2) NOT NULL,
  payment_method ENUM('pronto','transferencia','financiamento','misto') NOT NULL DEFAULT 'transferencia',
  final_channel VARCHAR(40) NOT NULL DEFAULT 'sem_canal',
  finance_company VARCHAR(190) NULL,
  finance_commission DECIMAL(12,2) NOT NULL DEFAULT 0,
  insurance_company VARCHAR(190) NULL,
  insurance_commission DECIMAL(12,2) NOT NULL DEFAULT 0,
  invoice_no VARCHAR(80) NULL,
  notes TEXT NULL,
  created_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_sale_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE,
  CONSTRAINT fk_sale_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT,
  CONSTRAINT fk_sale_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS generated_pdfs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pdf_type ENUM('contrato_venda','proposta','fatura_proforma','relatorio_viatura','outro') NOT NULL,
  vehicle_id INT NULL,
  customer_id INT NULL,
  sale_id INT NULL,
  title VARCHAR(190) NOT NULL,
  stored_filename VARCHAR(255) NOT NULL,
  sha256 CHAR(64) NOT NULL,
  created_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_pdf_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE SET NULL,
  CONSTRAINT fk_pdf_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
  CONSTRAINT fk_pdf_sale FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE SET NULL,
  CONSTRAINT fk_pdf_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);


-- V8 Migration: lead stage events + reports helpers
CREATE TABLE IF NOT EXISTS lead_stage_events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  lead_id INT NOT NULL,
  from_stage ENUM('novo','contactado','test_drive','proposta','financiamento','fechado','perdido') NULL,
  to_stage   ENUM('novo','contactado','test_drive','proposta','financiamento','fechado','perdido') NOT NULL,
  changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  changed_by INT NULL,
  notes VARCHAR(255) NULL,
  INDEX idx_lse_lead (lead_id),
  INDEX idx_lse_changed_at (changed_at),
  CONSTRAINT fk_lse_lead FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
  CONSTRAINT fk_lse_user FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE SET NULL
);


-- V9 Migration: customers code, vehicles owner fields, suppliers, supplier links
ALTER TABLE customers ADD COLUMN customer_code VARCHAR(30) NULL UNIQUE AFTER id;

ALTER TABLE vehicles 
  ADD COLUMN owner_type VARCHAR(60) NULL AFTER source,
  ADD COLUMN owner_name VARCHAR(190) NULL AFTER owner_type,
  ADD COLUMN supplier_id INT NULL AFTER supplier_name;

CREATE TABLE IF NOT EXISTS suppliers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  nif VARCHAR(30) NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(40) NULL,
  address VARCHAR(220) NULL,
  notes TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE vehicle_costs ADD COLUMN supplier_id INT NULL AFTER supplier;
ALTER TABLE documents ADD COLUMN supplier_id INT NULL AFTER supplier;

-- Indexes + FKs (safe)
CREATE INDEX idx_vehicle_supplier_id ON vehicles(supplier_id);
CREATE INDEX idx_cost_supplier_id ON vehicle_costs(supplier_id);
CREATE INDEX idx_doc_supplier_id ON documents(supplier_id);

ALTER TABLE vehicles
  ADD CONSTRAINT fk_vehicle_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL;

ALTER TABLE vehicle_costs
  ADD CONSTRAINT fk_cost_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL;

ALTER TABLE documents
  ADD CONSTRAINT fk_doc_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL;

