-- Vendors, purchases, counter sales, and workshop expenses.

ALTER TABLE parts_inventory
  MODIFY source_type ENUM('new_vehicle', 'accidental', 'donor_vehicle', 'purchase') NOT NULL;

CREATE TABLE IF NOT EXISTS vendors (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(80) NOT NULL,
  phone VARCHAR(20) DEFAULT NULL,
  gstin VARCHAR(20) DEFAULT NULL,
  address VARCHAR(255) DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_by INT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_vendors_active (is_active),
  CONSTRAINT fk_vendors_created_by FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS purchases (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  vendor_id INT UNSIGNED NOT NULL,
  invoice_number VARCHAR(40) NOT NULL,
  invoice_date DATE NOT NULL,
  subtotal DECIMAL(12, 2) NOT NULL DEFAULT 0,
  tax_amount DECIMAL(12, 2) NOT NULL DEFAULT 0,
  total_amount DECIMAL(12, 2) NOT NULL DEFAULT 0,
  invoice_file VARCHAR(500) DEFAULT NULL,
  notes VARCHAR(255) DEFAULT NULL,
  created_by INT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_purchases_date (invoice_date),
  CONSTRAINT fk_purchases_vendor FOREIGN KEY (vendor_id) REFERENCES vendors (id),
  CONSTRAINT fk_purchases_created_by FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS purchase_lines (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  purchase_id INT UNSIGNED NOT NULL,
  parts_inventory_id INT UNSIGNED DEFAULT NULL,
  part_name VARCHAR(150) NOT NULL,
  quantity DECIMAL(10, 2) NOT NULL,
  unit_cost DECIMAL(12, 2) NOT NULL,
  tax_percent DECIMAL(5, 2) NOT NULL DEFAULT 0,
  line_tax DECIMAL(12, 2) NOT NULL DEFAULT 0,
  line_total DECIMAL(12, 2) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY idx_purchase_lines_purchase (purchase_id),
  CONSTRAINT fk_purchase_lines_purchase FOREIGN KEY (purchase_id) REFERENCES purchases (id) ON DELETE CASCADE,
  CONSTRAINT fk_purchase_lines_part FOREIGN KEY (parts_inventory_id) REFERENCES parts_inventory (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS stock_sales (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  subtotal DECIMAL(12, 2) NOT NULL DEFAULT 0,
  tax_amount DECIMAL(12, 2) NOT NULL DEFAULT 0,
  total_amount DECIMAL(12, 2) NOT NULL DEFAULT 0,
  notes VARCHAR(255) DEFAULT NULL,
  sold_by INT UNSIGNED NOT NULL,
  sold_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_stock_sales_sold_at (sold_at),
  CONSTRAINT fk_stock_sales_sold_by FOREIGN KEY (sold_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS stock_sale_lines (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  stock_sale_id INT UNSIGNED NOT NULL,
  parts_inventory_id INT UNSIGNED NOT NULL,
  part_name VARCHAR(150) NOT NULL,
  quantity DECIMAL(10, 2) NOT NULL,
  unit_price DECIMAL(12, 2) NOT NULL,
  tax_percent DECIMAL(5, 2) NOT NULL DEFAULT 0,
  line_tax DECIMAL(12, 2) NOT NULL DEFAULT 0,
  line_total DECIMAL(12, 2) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY idx_stock_sale_lines_sale (stock_sale_id),
  CONSTRAINT fk_stock_sale_lines_sale FOREIGN KEY (stock_sale_id) REFERENCES stock_sales (id) ON DELETE CASCADE,
  CONSTRAINT fk_stock_sale_lines_part FOREIGN KEY (parts_inventory_id) REFERENCES parts_inventory (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS expense_types (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(80) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_expense_type_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS expenses (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  expense_type_id INT UNSIGNED NOT NULL,
  amount DECIMAL(12, 2) NOT NULL,
  expense_date DATE NOT NULL,
  note VARCHAR(255) DEFAULT NULL,
  recorded_by INT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_expenses_date (expense_date),
  CONSTRAINT fk_expenses_type FOREIGN KEY (expense_type_id) REFERENCES expense_types (id),
  CONSTRAINT fk_expenses_recorded_by FOREIGN KEY (recorded_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO expense_types (name)
SELECT seed.name FROM (
  SELECT 'Rent' AS name UNION ALL
  SELECT 'Electricity' UNION ALL
  SELECT 'Salary' UNION ALL
  SELECT 'Fuel' UNION ALL
  SELECT 'Tools'
) AS seed
WHERE NOT EXISTS (SELECT 1 FROM expense_types et WHERE et.name = seed.name);