PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,
  role TEXT DEFAULT 'admin',
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS settings (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  business_name TEXT NOT NULL,
  address TEXT,
  phone TEXT,
  email TEXT,
  gstin TEXT,
  state_name TEXT,
  state_code TEXT,
  logo_path TEXT,
  signature_path TEXT,
  seal_path TEXT,
  cgst_percent REAL DEFAULT 2.50,
  sgst_percent REAL DEFAULT 2.50,
  igst_percent REAL DEFAULT 5.00,
  default_hsn TEXT,
  invoice_prefix TEXT DEFAULT 'EF',
  invoice_financial_year TEXT,
  invoice_padding INTEGER DEFAULT 4,
  next_invoice_number INTEGER DEFAULT 1,
  declaration_text TEXT,
  terms_text TEXT,
  bank_details TEXT,
  updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS customers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  address TEXT,
  phone TEXT,
  email TEXT,
  gstin TEXT,
  state_name TEXT,
  state_code TEXT,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS invoices (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  invoice_number TEXT NOT NULL UNIQUE,
  invoice_date TEXT NOT NULL,
  customer_id INTEGER,
  customer_name TEXT NOT NULL,
  customer_address TEXT,
  customer_phone TEXT,
  customer_email TEXT,
  customer_gstin TEXT,
  customer_state_name TEXT,
  customer_state_code TEXT,
  consignee_name TEXT,
  consignee_address TEXT,
  consignee_gstin TEXT,
  consignee_state_name TEXT,
  consignee_state_code TEXT,
  payment_mode TEXT,
  delivery_note TEXT,
  buyer_order_no TEXT,
  dispatch_doc_no TEXT,
  delivery_date TEXT,
  destination TEXT,
  terms_of_delivery TEXT,
  taxable_amount REAL DEFAULT 0,
  cgst_amount REAL DEFAULT 0,
  sgst_amount REAL DEFAULT 0,
  igst_amount REAL DEFAULT 0,
  round_off REAL DEFAULT 0,
  grand_total REAL DEFAULT 0,
  amount_in_words TEXT,
  show_logo INTEGER DEFAULT 1,
  show_signature INTEGER DEFAULT 1,
  show_seal INTEGER DEFAULT 1,
  status TEXT DEFAULT 'active',
  created_at TEXT DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS invoice_items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  invoice_id INTEGER NOT NULL,
  description TEXT NOT NULL,
  paper_type TEXT NOT NULL,
  hsn_code TEXT,
  quantity REAL NOT NULL,
  rate REAL NOT NULL,
  discount_percent REAL DEFAULT 0,
  taxable_amount REAL NOT NULL,
  cgst_percent REAL DEFAULT 0,
  cgst_amount REAL DEFAULT 0,
  sgst_percent REAL DEFAULT 0,
  sgst_amount REAL DEFAULT 0,
  igst_percent REAL DEFAULT 0,
  igst_amount REAL DEFAULT 0,
  total_amount REAL NOT NULL,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
);
