-- Basic schema for merchant system
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(190) NOT NULL,
  store_name VARCHAR(190) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  id INT PRIMARY KEY CHECK (id=1),
  site_title VARCHAR(190) NOT NULL DEFAULT 'Merchant Pro',
  vat_enabled TINYINT(1) NOT NULL DEFAULT 1,
  default_vat_percent DECIMAL(6,2) NOT NULL DEFAULT 15.00,
  default_fees_percent DECIMAL(6,2) NOT NULL DEFAULT 0.00,
  always_show_inc_vat TINYINT(1) NOT NULL DEFAULT 1,
  logo_path VARCHAR(255) DEFAULT NULL,
  favicon_path VARCHAR(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO settings (id, site_title) VALUES (1, 'Merchant Pro')
  ON DUPLICATE KEY UPDATE site_title=VALUES(site_title);

CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(100) UNIQUE NULL,
  name VARCHAR(190) NOT NULL,
  category VARCHAR(190) NULL,
  default_vat_percent DECIMAL(6,2) NOT NULL DEFAULT 15.00,
  default_fees_percent DECIMAL(6,2) NOT NULL DEFAULT 0.00,
  desired_profit_percent DECIMAL(8,2) NOT NULL DEFAULT 20.00,
  min_profit_per_unit DECIMAL(12,4) NOT NULL DEFAULT 0.0000,
  low_stock_threshold INT NOT NULL DEFAULT 0,
  always_show_inc_vat TINYINT(1) NOT NULL DEFAULT 1,
  notes TEXT NULL,
  stock_qty INT NOT NULL DEFAULT 0,
  stock_avg_cost DECIMAL(12,4) NOT NULL DEFAULT 0.0000,
  image_path VARCHAR(255) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS purchases (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  qty INT NOT NULL,
  unit_cost DECIMAL(12,4) NOT NULL,
  notes VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sales (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  qty INT NOT NULL,
  unit_price_ex DECIMAL(12,4) NOT NULL,
  vat_percent DECIMAL(6,2) NOT NULL DEFAULT 15.00,
  fees_percent DECIMAL(6,2) NOT NULL DEFAULT 0.00,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;