CREATE TABLE sales (
  id VARCHAR(64) PRIMARY KEY,
  business_date DATE NOT NULL,
  payment_method ENUM('cash', 'credit') NOT NULL,
  subtotal DECIMAL(10,2) NOT NULL,
  tax DECIMAL(10,2) NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  amount_tendered DECIMAL(10,2) NULL,
  change_due DECIMAL(10,2) NULL,
  sold_at DATETIME NOT NULL,
  cashier_name VARCHAR(100) NOT NULL DEFAULT 'Cashier',
  sale_status VARCHAR(20) NOT NULL DEFAULT 'completed',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_sales_business_date (business_date),
  INDEX idx_sales_payment_method (payment_method),
  INDEX idx_sales_cashier_name (cashier_name),
  INDEX idx_sales_status (sale_status)
);

CREATE TABLE sale_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sale_id VARCHAR(64) NOT NULL,
  name VARCHAR(255) NOT NULL,
  category VARCHAR(100) NOT NULL,
  report_category VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  quantity INT NOT NULL,
  taxable TINYINT(1) NOT NULL DEFAULT 0,
  tax_rate DECIMAL(6,2) NULL,
  tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  line_total DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_sale_items_sale
    FOREIGN KEY (sale_id) REFERENCES sales(id)
    ON DELETE CASCADE,
  INDEX idx_sale_items_sale_id (sale_id),
  INDEX idx_sale_items_report_category (report_category)
);

CREATE TABLE settings (
  setting_key VARCHAR(100) PRIMARY KEY,
  setting_value JSON NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE catalog_products (
  id VARCHAR(64) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  category VARCHAR(100) NOT NULL,
  emoji VARCHAR(40) NOT NULL,
  image_url VARCHAR(255) NULL,
  taxable TINYINT(1) NOT NULL DEFAULT 0,
  tax_rate DECIMAL(6,2) NULL,
  report_category VARCHAR(100) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);