-- =====================================================================
-- Online ERP Accounting + Ecommerce System (Trader Edition v2)
-- Engine: MySQL / MariaDB, InnoDB, utf8mb4
-- =====================================================================
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- 1. ADMIN / STAFF / ROLES
-- ---------------------------------------------------------------------
CREATE TABLE roles (
  role_id INT AUTO_INCREMENT PRIMARY KEY,
  role_name VARCHAR(50) NOT NULL UNIQUE,
  permissions TEXT NULL, -- JSON encoded permission map
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE admins (
  admin_id INT AUTO_INCREMENT PRIMARY KEY,
  role_id INT NOT NULL,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  mobile VARCHAR(20) NULL,
  password_hash VARCHAR(255) NOT NULL,
  photo_blob LONGBLOB NULL,
  photo_mime VARCHAR(50) NULL,
  status ENUM('active','inactive') DEFAULT 'active',
  last_login DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (role_id) REFERENCES roles(role_id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 2. VENDORS (Multi-vendor marketplace)
-- ---------------------------------------------------------------------
CREATE TABLE vendors (
  vendor_id INT AUTO_INCREMENT PRIMARY KEY,
  business_name VARCHAR(150) NOT NULL,
  owner_name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  mobile VARCHAR(20) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  gst_no VARCHAR(20) NULL,
  pan_no VARCHAR(20) NULL,
  address VARCHAR(255) NULL,
  city VARCHAR(100) NULL,
  state VARCHAR(100) NULL,
  pincode VARCHAR(10) NULL,
  logo_blob LONGBLOB NULL,
  logo_mime VARCHAR(50) NULL,
  bank_account_name VARCHAR(150) NULL,
  bank_account_no VARCHAR(50) NULL,
  bank_ifsc VARCHAR(20) NULL,
  bank_name VARCHAR(100) NULL,
  commission_percent DECIMAL(5,2) DEFAULT 10.00, -- platform commission
  status ENUM('pending','approved','suspended','rejected') DEFAULT 'pending',
  approved_by INT NULL,
  approved_at DATETIME NULL,
  ledger_account_id INT NULL, -- linked to chart_of_accounts, for payouts
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (approved_by) REFERENCES admins(admin_id)
) ENGINE=InnoDB;

CREATE TABLE vendor_payouts (
  payout_id INT AUTO_INCREMENT PRIMARY KEY,
  vendor_id INT NOT NULL,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  gross_sales DECIMAL(14,2) NOT NULL DEFAULT 0,
  commission_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  net_payable DECIMAL(14,2) NOT NULL DEFAULT 0,
  status ENUM('pending','paid') DEFAULT 'pending',
  paid_on DATE NULL,
  payment_ref VARCHAR(100) NULL,
  processed_by INT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (vendor_id) REFERENCES vendors(vendor_id),
  FOREIGN KEY (processed_by) REFERENCES admins(admin_id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 3. WAREHOUSES / STOCK TRANSFER
-- ---------------------------------------------------------------------
CREATE TABLE warehouses (
  warehouse_id INT AUTO_INCREMENT PRIMARY KEY,
  warehouse_name VARCHAR(100) NOT NULL,
  code VARCHAR(20) NOT NULL UNIQUE,
  address VARCHAR(255) NULL,
  city VARCHAR(100) NULL,
  state VARCHAR(100) NULL,
  is_default TINYINT(1) DEFAULT 0,
  status ENUM('active','inactive') DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE stock_transfers (
  transfer_id INT AUTO_INCREMENT PRIMARY KEY,
  transfer_no VARCHAR(30) NOT NULL UNIQUE,
  from_warehouse_id INT NOT NULL,
  to_warehouse_id INT NOT NULL,
  status ENUM('draft','dispatched','received','cancelled') DEFAULT 'draft',
  remarks VARCHAR(255) NULL,
  created_by INT NULL,
  received_by INT NULL,
  dispatched_at DATETIME NULL,
  received_at DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (from_warehouse_id) REFERENCES warehouses(warehouse_id),
  FOREIGN KEY (to_warehouse_id) REFERENCES warehouses(warehouse_id),
  FOREIGN KEY (created_by) REFERENCES admins(admin_id),
  FOREIGN KEY (received_by) REFERENCES admins(admin_id)
) ENGINE=InnoDB;

CREATE TABLE stock_transfer_items (
  item_id INT AUTO_INCREMENT PRIMARY KEY,
  transfer_id INT NOT NULL,
  product_id INT NOT NULL,
  qty DECIMAL(12,2) NOT NULL,
  received_qty DECIMAL(12,2) DEFAULT 0,
  FOREIGN KEY (transfer_id) REFERENCES stock_transfers(transfer_id)
) ENGINE=InnoDB;

-- per-warehouse stock ledger (replaces a single flat stock_qty column)
CREATE TABLE product_warehouse_stock (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  warehouse_id INT NOT NULL,
  qty DECIMAL(12,2) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_prod_wh (product_id, warehouse_id),
  FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 4. CATEGORIES / BRANDS / PRODUCTS
-- ---------------------------------------------------------------------
CREATE TABLE categories (
  category_id INT AUTO_INCREMENT PRIMARY KEY,
  category_name VARCHAR(100) NOT NULL,
  slug VARCHAR(120) NOT NULL UNIQUE,
  parent_id INT NULL,
  image_blob LONGBLOB NULL,
  image_mime VARCHAR(50) NULL,
  show_in_slider TINYINT(1) DEFAULT 1,
  sort_order INT DEFAULT 0,
  status ENUM('active','inactive') DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (parent_id) REFERENCES categories(category_id)
) ENGINE=InnoDB;

CREATE TABLE brands (
  brand_id INT AUTO_INCREMENT PRIMARY KEY,
  brand_name VARCHAR(100) NOT NULL,
  slug VARCHAR(120) NOT NULL UNIQUE,
  logo_blob LONGBLOB NULL,
  logo_mime VARCHAR(50) NULL,
  show_in_slider TINYINT(1) DEFAULT 1,
  sort_order INT DEFAULT 0,
  status ENUM('active','inactive') DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE products (
  product_id INT AUTO_INCREMENT PRIMARY KEY,
  vendor_id INT NULL, -- NULL = own-stock (trader's own product), else marketplace vendor's product
  category_id INT NOT NULL,
  brand_id INT NULL,
  sku VARCHAR(50) NOT NULL UNIQUE,
  product_name VARCHAR(200) NOT NULL,
  slug VARCHAR(220) NOT NULL UNIQUE,
  description LONGTEXT NULL, -- CKEditor HTML
  hsn_code VARCHAR(20) NULL,
  gst_percent DECIMAL(5,2) NOT NULL DEFAULT 18.00,
  unit VARCHAR(20) DEFAULT 'PCS',
  mrp DECIMAL(12,2) NOT NULL DEFAULT 0,
  selling_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  purchase_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  reorder_level DECIMAL(12,2) DEFAULT 0,
  is_trending TINYINT(1) DEFAULT 0,
  is_coming_soon TINYINT(1) DEFAULT 0,
  status ENUM('active','inactive') DEFAULT 'active',
  approval_status ENUM('approved','pending','rejected') DEFAULT 'approved', -- vendor products need admin approval
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (vendor_id) REFERENCES vendors(vendor_id),
  FOREIGN KEY (category_id) REFERENCES categories(category_id),
  FOREIGN KEY (brand_id) REFERENCES brands(brand_id)
) ENGINE=InnoDB;

CREATE TABLE product_images (
  image_id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  image_blob LONGBLOB NOT NULL,
  image_mime VARCHAR(50) NOT NULL,
  sort_order INT DEFAULT 0,
  is_primary TINYINT(1) DEFAULT 0,
  FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 5. CUSTOMERS
-- ---------------------------------------------------------------------
CREATE TABLE customers (
  customer_id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  mobile VARCHAR(20) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  gst_no VARCHAR(20) NULL,
  photo_blob LONGBLOB NULL,
  photo_mime VARCHAR(50) NULL,
  qrcode_blob LONGBLOB NULL, -- generated QR (customer id card / loyalty)
  qrcode_mime VARCHAR(50) NULL,
  address VARCHAR(255) NULL,
  city VARCHAR(100) NULL,
  state VARCHAR(100) NULL,
  pincode VARCHAR(10) NULL,
  bank_account_name VARCHAR(150) NULL,
  bank_account_no VARCHAR(50) NULL,
  bank_ifsc VARCHAR(20) NULL,
  bank_name VARCHAR(100) NULL, -- used for refunds/reverts
  status ENUM('active','inactive') DEFAULT 'active',
  email_opt_in TINYINT(1) DEFAULT 1,
  sms_opt_in TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE customer_addresses (
  address_id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  label VARCHAR(50) DEFAULT 'Home',
  full_address VARCHAR(255) NOT NULL,
  city VARCHAR(100) NOT NULL,
  state VARCHAR(100) NOT NULL,
  pincode VARCHAR(10) NOT NULL,
  mobile VARCHAR(20) NOT NULL,
  is_default TINYINT(1) DEFAULT 0,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE wishlist (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  product_id INT NOT NULL,
  added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_wish (customer_id, product_id),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE buy_later (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  product_id INT NOT NULL,
  qty INT DEFAULT 1,
  added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE cart (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  product_id INT NOT NULL,
  qty INT NOT NULL DEFAULT 1,
  added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 6. SUPPLIERS / DELIVERY PARTNERS
-- ---------------------------------------------------------------------
CREATE TABLE suppliers (
  supplier_id INT AUTO_INCREMENT PRIMARY KEY,
  supplier_name VARCHAR(150) NOT NULL,
  gst_no VARCHAR(20) NULL,
  contact_person VARCHAR(100) NULL,
  mobile VARCHAR(20) NULL,
  email VARCHAR(150) NULL,
  address VARCHAR(255) NULL,
  city VARCHAR(100) NULL,
  state VARCHAR(100) NULL,
  photo_blob LONGBLOB NULL,
  photo_mime VARCHAR(50) NULL,
  bank_account_name VARCHAR(150) NULL,
  bank_account_no VARCHAR(50) NULL,
  bank_ifsc VARCHAR(20) NULL,
  bank_name VARCHAR(100) NULL,
  opening_balance DECIMAL(14,2) DEFAULT 0,
  ledger_account_id INT NULL,
  status ENUM('active','inactive') DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE delivery_partners (
  partner_id INT AUTO_INCREMENT PRIMARY KEY,
  partner_name VARCHAR(100) NOT NULL,
  mobile VARCHAR(20) NULL,
  email VARCHAR(150) NULL,
  vehicle_no VARCHAR(30) NULL,
  photo_blob LONGBLOB NULL,
  photo_mime VARCHAR(50) NULL,
  status ENUM('active','inactive') DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 7. ACCOUNTING CORE (Chart of Accounts + Ledger)
-- ---------------------------------------------------------------------
CREATE TABLE account_groups (
  group_id INT AUTO_INCREMENT PRIMARY KEY,
  group_name VARCHAR(100) NOT NULL,
  nature ENUM('asset','liability','equity','income','expense') NOT NULL,
  parent_id INT NULL,
  FOREIGN KEY (parent_id) REFERENCES account_groups(group_id)
) ENGINE=InnoDB;

CREATE TABLE chart_of_accounts (
  account_id INT AUTO_INCREMENT PRIMARY KEY,
  group_id INT NOT NULL,
  account_name VARCHAR(150) NOT NULL,
  account_code VARCHAR(30) NULL,
  opening_balance DECIMAL(14,2) DEFAULT 0,
  opening_balance_type ENUM('dr','cr') DEFAULT 'dr',
  is_system TINYINT(1) DEFAULT 0, -- system accounts (Cash, Sales, GST) not deletable
  status ENUM('active','inactive') DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (group_id) REFERENCES account_groups(group_id)
) ENGINE=InnoDB;

CREATE TABLE ledger_entries (
  entry_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  voucher_type VARCHAR(30) NOT NULL, -- sales, purchase, payment, receipt, journal, pos
  voucher_no VARCHAR(30) NOT NULL,
  voucher_date DATE NOT NULL,
  account_id INT NOT NULL,
  dr_amount DECIMAL(14,2) DEFAULT 0,
  cr_amount DECIMAL(14,2) DEFAULT 0,
  narration VARCHAR(255) NULL,
  reference_table VARCHAR(50) NULL, -- e.g. 'sales_invoices'
  reference_id INT NULL,
  created_by INT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (account_id) REFERENCES chart_of_accounts(account_id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 8. BANK / UPI ACCOUNTS
-- ---------------------------------------------------------------------
CREATE TABLE bank_accounts (
  bank_account_id INT AUTO_INCREMENT PRIMARY KEY,
  account_id INT NOT NULL, -- linked ledger account
  bank_name VARCHAR(100) NOT NULL,
  account_no VARCHAR(50) NOT NULL,
  ifsc VARCHAR(20) NULL,
  branch VARCHAR(100) NULL,
  upi_id VARCHAR(100) NULL,
  qr_blob LONGBLOB NULL,
  qr_mime VARCHAR(50) NULL,
  status ENUM('active','inactive') DEFAULT 'active',
  FOREIGN KEY (account_id) REFERENCES chart_of_accounts(account_id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 9. SALES / PURCHASE INVOICES
-- ---------------------------------------------------------------------
CREATE TABLE sales_invoices (
  invoice_id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_no VARCHAR(30) NOT NULL UNIQUE,
  invoice_type ENUM('local','online') NOT NULL DEFAULT 'local',
  customer_id INT NULL,
  vendor_id INT NULL, -- if this invoice is for a marketplace vendor's sale
  warehouse_id INT NULL,
  invoice_date DATE NOT NULL,
  subtotal DECIMAL(14,2) NOT NULL DEFAULT 0,
  discount_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  cgst_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  sgst_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  igst_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  round_off DECIMAL(6,2) NOT NULL DEFAULT 0,
  grand_total DECIMAL(14,2) NOT NULL DEFAULT 0,
  payment_mode ENUM('cash','upi','card','online','credit') DEFAULT 'cash',
  payment_status ENUM('paid','partial','unpaid') DEFAULT 'unpaid',
  order_id INT NULL,
  created_by INT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
  FOREIGN KEY (vendor_id) REFERENCES vendors(vendor_id),
  FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
) ENGINE=InnoDB;

CREATE TABLE sales_invoice_items (
  item_id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT NOT NULL,
  product_id INT NOT NULL,
  qty DECIMAL(12,2) NOT NULL,
  rate DECIMAL(12,2) NOT NULL,
  gst_percent DECIMAL(5,2) NOT NULL DEFAULT 0,
  taxable_value DECIMAL(14,2) NOT NULL,
  cgst_amount DECIMAL(14,2) DEFAULT 0,
  sgst_amount DECIMAL(14,2) DEFAULT 0,
  igst_amount DECIMAL(14,2) DEFAULT 0,
  line_total DECIMAL(14,2) NOT NULL,
  FOREIGN KEY (invoice_id) REFERENCES sales_invoices(invoice_id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(product_id)
) ENGINE=InnoDB;

CREATE TABLE purchase_invoices (
  purchase_id INT AUTO_INCREMENT PRIMARY KEY,
  purchase_no VARCHAR(30) NOT NULL UNIQUE,
  supplier_id INT NOT NULL,
  warehouse_id INT NULL,
  purchase_date DATE NOT NULL,
  subtotal DECIMAL(14,2) NOT NULL DEFAULT 0,
  discount_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  cgst_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  sgst_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  igst_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  grand_total DECIMAL(14,2) NOT NULL DEFAULT 0,
  payment_status ENUM('paid','partial','unpaid') DEFAULT 'unpaid',
  created_by INT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id),
  FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
) ENGINE=InnoDB;

CREATE TABLE purchase_invoice_items (
  item_id INT AUTO_INCREMENT PRIMARY KEY,
  purchase_id INT NOT NULL,
  product_id INT NOT NULL,
  qty DECIMAL(12,2) NOT NULL,
  rate DECIMAL(12,2) NOT NULL,
  gst_percent DECIMAL(5,2) NOT NULL DEFAULT 0,
  taxable_value DECIMAL(14,2) NOT NULL,
  cgst_amount DECIMAL(14,2) DEFAULT 0,
  sgst_amount DECIMAL(14,2) DEFAULT 0,
  igst_amount DECIMAL(14,2) DEFAULT 0,
  line_total DECIMAL(14,2) NOT NULL,
  FOREIGN KEY (purchase_id) REFERENCES purchase_invoices(purchase_id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(product_id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 10. ORDERS (Ecommerce checkout -> fulfilment)
-- ---------------------------------------------------------------------
CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  order_no VARCHAR(30) NOT NULL UNIQUE,
  customer_id INT NOT NULL,
  address_id INT NULL,
  delivery_partner_id INT NULL,
  warehouse_id INT NULL,
  subtotal DECIMAL(14,2) NOT NULL DEFAULT 0,
  discount_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  promo_code VARCHAR(30) NULL,
  gst_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  shipping_charge DECIMAL(12,2) NOT NULL DEFAULT 0,
  grand_total DECIMAL(14,2) NOT NULL DEFAULT 0,
  payment_mode ENUM('cod','upi','card','online') DEFAULT 'cod',
  payment_status ENUM('pending','paid','failed','refunded') DEFAULT 'pending',
  order_status ENUM('placed','confirmed','packed','shipped','out_for_delivery','delivered','cancelled','returned') DEFAULT 'placed',
  tracking_no VARCHAR(60) NULL,
  invoice_id INT NULL,
  placed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
  FOREIGN KEY (address_id) REFERENCES customer_addresses(address_id),
  FOREIGN KEY (delivery_partner_id) REFERENCES delivery_partners(partner_id),
  FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
) ENGINE=InnoDB;

CREATE TABLE order_items (
  item_id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  vendor_id INT NULL,
  qty INT NOT NULL,
  rate DECIMAL(12,2) NOT NULL,
  line_total DECIMAL(14,2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(product_id)
) ENGINE=InnoDB;

CREATE TABLE order_tracking_history (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  status VARCHAR(30) NOT NULL,
  remarks VARCHAR(255) NULL,
  updated_by INT NULL,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 11. VOUCHERS (Payment/Receipt) + Books
-- ---------------------------------------------------------------------
CREATE TABLE vouchers (
  voucher_id INT AUTO_INCREMENT PRIMARY KEY,
  voucher_type ENUM('payment','receipt') NOT NULL,
  voucher_no VARCHAR(30) NOT NULL UNIQUE,
  voucher_date DATE NOT NULL,
  pay_from_account_id INT NOT NULL, -- cash/bank account
  pay_to_account_id INT NOT NULL,   -- party/expense account
  amount DECIMAL(14,2) NOT NULL,
  mode ENUM('cash','bank','upi','cheque') DEFAULT 'cash',
  narration VARCHAR(255) NULL,
  created_by INT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (pay_from_account_id) REFERENCES chart_of_accounts(account_id),
  FOREIGN KEY (pay_to_account_id) REFERENCES chart_of_accounts(account_id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 12. GST RETURNS
-- ---------------------------------------------------------------------
CREATE TABLE gst_returns (
  return_id INT AUTO_INCREMENT PRIMARY KEY,
  return_type ENUM('GSTR1','GSTR3B') NOT NULL,
  period_month TINYINT NOT NULL,
  period_year SMALLINT NOT NULL,
  total_taxable_value DECIMAL(14,2) DEFAULT 0,
  total_cgst DECIMAL(14,2) DEFAULT 0,
  total_sgst DECIMAL(14,2) DEFAULT 0,
  total_igst DECIMAL(14,2) DEFAULT 0,
  status ENUM('draft','filed') DEFAULT 'draft',
  filed_on DATE NULL,
  UNIQUE KEY uq_gst_period (return_type, period_month, period_year)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 13. PROMOTIONS: promo codes / offers / schemes / banners
-- ---------------------------------------------------------------------
CREATE TABLE promo_codes (
  promo_id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(30) NOT NULL UNIQUE,
  discount_type ENUM('flat','percent') NOT NULL,
  discount_value DECIMAL(10,2) NOT NULL,
  min_order_value DECIMAL(12,2) DEFAULT 0,
  max_discount DECIMAL(12,2) NULL,
  usage_limit INT NULL,
  used_count INT DEFAULT 0,
  valid_from DATE NULL,
  valid_to DATE NULL,
  status ENUM('active','inactive') DEFAULT 'active'
) ENGINE=InnoDB;

CREATE TABLE product_offers (
  offer_id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  discount_percent DECIMAL(5,2) NOT NULL,
  valid_from DATE NULL,
  valid_to DATE NULL,
  status ENUM('active','inactive') DEFAULT 'active',
  FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE sales_schemes (
  scheme_id INT AUTO_INCREMENT PRIMARY KEY,
  scheme_name VARCHAR(150) NOT NULL,
  description VARCHAR(255) NULL,
  buy_qty INT NULL,
  free_qty INT NULL,
  applicable_category_id INT NULL,
  valid_from DATE NULL,
  valid_to DATE NULL,
  status ENUM('active','inactive') DEFAULT 'active',
  FOREIGN KEY (applicable_category_id) REFERENCES categories(category_id)
) ENGINE=InnoDB;

CREATE TABLE banners (
  banner_id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(150) NULL,
  image_blob LONGBLOB NOT NULL,
  image_mime VARCHAR(50) NOT NULL,
  link_url VARCHAR(255) NULL,
  sort_order INT DEFAULT 0,
  status ENUM('active','inactive') DEFAULT 'active'
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 14. MARKETING CAMPAIGNS (SMS / Email)
-- ---------------------------------------------------------------------
CREATE TABLE marketing_campaigns (
  campaign_id INT AUTO_INCREMENT PRIMARY KEY,
  campaign_name VARCHAR(150) NOT NULL,
  channel ENUM('sms','email') NOT NULL,
  subject VARCHAR(200) NULL, -- email only
  message_body TEXT NOT NULL,
  audience ENUM('all','city','state','tag') DEFAULT 'all',
  audience_filter VARCHAR(150) NULL, -- e.g. city name
  scheduled_at DATETIME NULL,
  status ENUM('draft','scheduled','sending','sent','failed') DEFAULT 'draft',
  total_recipients INT DEFAULT 0,
  sent_count INT DEFAULT 0,
  failed_count INT DEFAULT 0,
  created_by INT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES admins(admin_id)
) ENGINE=InnoDB;

CREATE TABLE campaign_recipients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  campaign_id INT NOT NULL,
  customer_id INT NOT NULL,
  status ENUM('pending','sent','failed') DEFAULT 'pending',
  sent_at DATETIME NULL,
  error_message VARCHAR(255) NULL,
  FOREIGN KEY (campaign_id) REFERENCES marketing_campaigns(campaign_id) ON DELETE CASCADE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 15. WHATSAPP ENQUIRIES
-- ---------------------------------------------------------------------
CREATE TABLE product_enquiries (
  enquiry_id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  customer_id INT NULL,
  name VARCHAR(100) NULL,
  mobile VARCHAR(20) NULL,
  message VARCHAR(255) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (product_id) REFERENCES products(product_id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 16. SITE SETTINGS
-- ---------------------------------------------------------------------
CREATE TABLE site_settings (
  setting_key VARCHAR(100) PRIMARY KEY,
  setting_value TEXT NULL
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;

-- ---------------------------------------------------------------------
-- Seed: default roles, account groups, system accounts, warehouse
-- ---------------------------------------------------------------------
INSERT INTO roles (role_name, permissions) VALUES
('Super Admin', '{"all":true}'),
('Accountant', '{"accounts":true,"reports":true}'),
('Sales Staff', '{"pos":true,"sales":true}');

INSERT INTO account_groups (group_name, nature) VALUES
('Current Assets','asset'),('Fixed Assets','asset'),
('Current Liabilities','liability'),('Capital','equity'),
('Direct Income','income'),('Indirect Income','income'),
('Direct Expenses','expense'),('Indirect Expenses','expense'),
('Sundry Debtors','asset'),('Sundry Creditors','liability'),
('Bank Accounts','asset'),('Cash-in-Hand','asset'),
('Duties & Taxes','liability');

INSERT INTO chart_of_accounts (group_id, account_name, is_system) VALUES
((SELECT group_id FROM account_groups WHERE group_name='Cash-in-Hand'), 'Cash', 1),
((SELECT group_id FROM account_groups WHERE group_name='Direct Income'), 'Sales Account', 1),
((SELECT group_id FROM account_groups WHERE group_name='Direct Expenses'), 'Purchase Account', 1),
((SELECT group_id FROM account_groups WHERE group_name='Duties & Taxes'), 'Output CGST', 1),
((SELECT group_id FROM account_groups WHERE group_name='Duties & Taxes'), 'Output SGST', 1),
((SELECT group_id FROM account_groups WHERE group_name='Duties & Taxes'), 'Output IGST', 1),
((SELECT group_id FROM account_groups WHERE group_name='Duties & Taxes'), 'Input CGST', 1),
((SELECT group_id FROM account_groups WHERE group_name='Duties & Taxes'), 'Input SGST', 1),
((SELECT group_id FROM account_groups WHERE group_name='Duties & Taxes'), 'Input IGST', 1),
((SELECT group_id FROM account_groups WHERE group_name='Indirect Expenses'), 'Vendor Commission Income', 1);

INSERT INTO warehouses (warehouse_name, code, is_default) VALUES ('Main Warehouse','MAIN',1);

INSERT INTO site_settings (setting_key, setting_value) VALUES
('site_name','My Trader Store'),
('site_gst_no',''),
('site_address',''),
('site_mobile',''),
('site_email',''),
('whatsapp_number',''),
('currency_symbol','₹'),
('vendor_commission_default','10'),
('smtp_host',''),
('smtp_port','587'),
('smtp_username',''),
('smtp_password',''),
('smtp_encryption','tls'),
('smtp_from_email',''),
('backup_retention_count','14');