-- MySQL schema for Consultants Portal (starter)
CREATE TABLE consultants (
  id INT AUTO_INCREMENT PRIMARY KEY,
  consultant_id VARCHAR(64) NOT NULL UNIQUE,
  name VARCHAR(150),
  email VARCHAR(150),
  phone VARCHAR(30),
  password_hash VARCHAR(255),
  bio TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE clients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id VARCHAR(64) NOT NULL UNIQUE,
  name VARCHAR(150),
  email VARCHAR(150),
  phone VARCHAR(30),
  password_hash VARCHAR(255),
  organization VARCHAR(150),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE inquiries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id VARCHAR(64),
  consultant_id VARCHAR(64),
  subject VARCHAR(255),
  message TEXT,
  attachment VARCHAR(255),
  status ENUM('open','quoted','invoiced','closed') DEFAULT 'open',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE quotations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  inquiry_id INT,
  consultant_id VARCHAR(64),
  quote_ref VARCHAR(64) UNIQUE,
  items JSON,
  total DECIMAL(12,2),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_ref VARCHAR(64) UNIQUE,
  quotation_id INT,
  client_id VARCHAR(64),
  amount DECIMAL(12,2),
  status ENUM('unpaid','paid') DEFAULT 'unpaid',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE uploads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  owner_type ENUM('consultant','client'),
  owner_id VARCHAR(64),
  filename VARCHAR(255),
  filepath VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);