CREATE DATABASE IF NOT EXISTS shahana_veg CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE shahana_veg;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(150) NOT NULL,
  username VARCHAR(80) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','staff','customer') NOT NULL,
  account_type ENUM('internal','wholesale','retail') DEFAULT 'internal',
  shop_name VARCHAR(150) DEFAULT NULL,
  phone VARCHAR(30) DEFAULT NULL,
  address TEXT DEFAULT NULL,
  id_proof_number VARCHAR(100) DEFAULT NULL,
  portal_access ENUM('admin','customer','both') NOT NULL DEFAULT 'both',
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  password_changed_at DATETIME DEFAULT NULL,
  password_expires_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  item_name VARCHAR(150) NOT NULL,
  section_type ENUM('vegetable','other') NOT NULL,
  unit_type ENUM('kg','quantity') NOT NULL,
  original_price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  wholesale_price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  retail_price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  stock_qty DECIMAL(10,2) DEFAULT 0.00,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_no VARCHAR(40) NOT NULL UNIQUE,
  customer_id INT DEFAULT NULL,
  customer_type ENUM('wholesale','retail') NOT NULL,
  billed_by INT NOT NULL,
  subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  discount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  installment_adjusted DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  invoice_date DATETIME NOT NULL,
  status ENUM('active','cancelled') NOT NULL DEFAULT 'active',
  can_edit_by_staff TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (customer_id) REFERENCES users(id),
  FOREIGN KEY (billed_by) REFERENCES users(id)
);

CREATE TABLE invoice_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT NOT NULL,
  item_id INT NOT NULL,
  item_name VARCHAR(150) NOT NULL,
  quantity DECIMAL(10,2) NOT NULL,
  unit_label VARCHAR(20) NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  line_total DECIMAL(12,2) NOT NULL,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
  FOREIGN KEY (item_id) REFERENCES items(id)
);

CREATE TABLE expenses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  expense_title VARCHAR(150) NOT NULL,
  category VARCHAR(80) NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  expense_date DATE NOT NULL,
  note TEXT DEFAULT NULL,
  added_by INT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (added_by) REFERENCES users(id)
);

CREATE TABLE installments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  entry_type ENUM('payment','purchase_adjustment') NOT NULL,
  reference_invoice_id INT DEFAULT NULL,
  amount DECIMAL(12,2) NOT NULL,
  note TEXT DEFAULT NULL,
  entry_date DATETIME NOT NULL,
  added_by INT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES users(id),
  FOREIGN KEY (reference_invoice_id) REFERENCES invoices(id),
  FOREIGN KEY (added_by) REFERENCES users(id)
);

CREATE TABLE contacts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  contact_name VARCHAR(150) NOT NULL,
  company_name VARCHAR(150) DEFAULT NULL,
  phone VARCHAR(30) DEFAULT NULL,
  email VARCHAR(120) DEFAULT NULL,
  address TEXT DEFAULT NULL,
  note TEXT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);