-- Create database myledger and user tables
CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  account_type ENUM('personal','business') NOT NULL DEFAULT 'personal',
  lang VARCHAR(5) NOT NULL DEFAULT 'en',
  theme ENUM('light','dark') NOT NULL DEFAULT 'light',
  role ENUM('user','admin') NOT NULL DEFAULT 'user',
  avatar VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS password_resets (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  token CHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Categories (user-defined)
CREATE TABLE IF NOT EXISTS categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  type ENUM('income','expense','loan','debt','product') NOT NULL,
  name VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_user_type_name (user_id,type,name),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Transactions (generic ledger rows for personal/business cashflow)
CREATE TABLE IF NOT EXISTS transactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  category_id INT UNSIGNED NULL,
  kind ENUM('income','expense','loan_given','loan_received','debt_taken','debt_paid') NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'KWD',
  occurred_on DATE NOT NULL,
  description TEXT NULL,
  attachment_path VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user_date (user_id, occurred_on),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Business: products, purchases, sales
CREATE TABLE IF NOT EXISTS products (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  sku VARCHAR(100) NOT NULL,
  name VARCHAR(200) NOT NULL,
  category_id INT UNSIGNED NULL,
  cost_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  sell_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  stock_qty DECIMAL(12,3) NOT NULL DEFAULT 0,
  unit VARCHAR(20) NOT NULL DEFAULT 'pcs',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_user_sku (user_id, sku),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS purchases (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  supplier_name VARCHAR(200) NULL,
  invoice_no VARCHAR(100) NULL,
  total DECIMAL(12,2) NOT NULL DEFAULT 0,
  occurred_on DATE NOT NULL,
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS purchase_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  purchase_id BIGINT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  qty DECIMAL(12,3) NOT NULL,
  cost DECIMAL(12,2) NOT NULL,
  FOREIGN KEY (purchase_id) REFERENCES purchases(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sales (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  customer_name VARCHAR(200) NULL,
  invoice_no VARCHAR(100) NULL,
  total DECIMAL(12,2) NOT NULL DEFAULT 0,
  occurred_on DATE NOT NULL,
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sale_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sale_id BIGINT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  qty DECIMAL(12,3) NOT NULL,
  price DECIMAL(12,2) NOT NULL,
  FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- App settings (admin panel)
CREATE TABLE IF NOT EXISTS settings (
  id TINYINT UNSIGNED PRIMARY KEY CHECK (id=1),
  site_title VARCHAR(200) NOT NULL DEFAULT 'MyLedger',
  meta_description VARCHAR(255) NULL,
  mail_driver ENUM('smtp','sendmail','none') NOT NULL DEFAULT 'none',
  mail_host VARCHAR(120) NULL,
  mail_port INT NULL,
  mail_user VARCHAR(120) NULL,
  mail_pass VARCHAR(120) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

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


-- === Users: country & currency ===
ALTER TABLE users
  ADD COLUMN country_code CHAR(2) NULL AFTER role,
  ADD COLUMN currency CHAR(3) NOT NULL DEFAULT 'KWD' AFTER country_code;

-- === App settings: defaults & toggles ===
ALTER TABLE settings
  ADD COLUMN default_language VARCHAR(5) NOT NULL DEFAULT 'en',
  ADD COLUMN default_currency CHAR(3) NOT NULL DEFAULT 'KWD',
  ADD COLUMN uploads_max_mb INT NOT NULL DEFAULT 10;

-- === Languages management ===
CREATE TABLE IF NOT EXISTS languages (
  code VARCHAR(5) PRIMARY KEY,       -- en, bn, ar, etc.
  name VARCHAR(60) NOT NULL,         -- English, বাংলা, العربية
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  rtl TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO languages (code,name,enabled,rtl)
VALUES ('en','English',1,0), ('bn','বাংলা',1,0), ('ar','العربية',1,1)
ON DUPLICATE KEY UPDATE name=VALUES(name), enabled=VALUES(enabled), rtl=VALUES(rtl);

-- (Optional) create an initial admin
INSERT INTO users (name,email,password_hash,account_type,lang,theme,role,country_code,currency)
VALUES ('Admin','admin@myledger.local', '$2y$10$8vZtQ2K8w2HkW0G1qO5x7u7iN5t4e02j8D2oK8a0d1E3iX1kD1mQK', 'personal','en','light','admin','KW','KWD')
ON DUPLICATE KEY UPDATE email=email;
-- Password here is a dummy hash; replace later.

