CREATE TABLE IF NOT EXISTS companies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  code VARCHAR(40) NOT NULL UNIQUE,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS company_templates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  original_filename VARCHAR(255) NOT NULL,
  stored_filename VARCHAR(255) NOT NULL,
  uploaded_at DATETIME NOT NULL,
  CONSTRAINT fk_templates_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS company_fields (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  field_key VARCHAR(80) NOT NULL,
  tag VARCHAR(80) NOT NULL,
  label VARCHAR(120) NOT NULL,
  field_type VARCHAR(20) NOT NULL DEFAULT 'text',
  is_fixed TINYINT(1) NOT NULL DEFAULT 0,
  default_value TEXT NULL,
  is_required TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_company_field (company_id, field_key),
  UNIQUE KEY uq_company_tag (company_id, tag),
  CONSTRAINT fk_fields_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  invoice_no VARCHAR(60) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_company_invoice (company_id, invoice_no),
  CONSTRAINT fk_invoices_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS invoice_values (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT NOT NULL,
  field_key VARCHAR(80) NOT NULL,
  tag VARCHAR(80) NOT NULL,
  value TEXT NULL,
  UNIQUE KEY uq_invoice_field (invoice_id, field_key),
  CONSTRAINT fk_values_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS invoice_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT NOT NULL,
  line_no INT NOT NULL,
  description TEXT NULL,
  model VARCHAR(200) NULL,
  size_inches VARCHAR(30) NULL,
  qty DECIMAL(12,2) NOT NULL DEFAULT 0,
  unit_price DECIMAL(12,4) NOT NULL DEFAULT 0,
  amount DECIMAL(12,4) NOT NULL DEFAULT 0,
  CONSTRAINT fk_items_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
  INDEX ix_invoice_line (invoice_id, line_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;