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

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin') DEFAULT 'admin',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  business_name VARCHAR(150) NOT NULL,
  address TEXT,
  phone VARCHAR(30),
  email VARCHAR(150),
  gstin VARCHAR(30),
  state_name VARCHAR(100),
  state_code VARCHAR(10),
  logo_path VARCHAR(255),
  signature_path VARCHAR(255),
  seal_path VARCHAR(255),
  cgst_percent DECIMAL(5,2) DEFAULT 2.50,
  sgst_percent DECIMAL(5,2) DEFAULT 2.50,
  igst_percent DECIMAL(5,2) DEFAULT 5.00,
  default_hsn VARCHAR(20),
  invoice_prefix VARCHAR(50) DEFAULT 'EF',
  invoice_financial_year VARCHAR(20),
  invoice_padding INT DEFAULT 4,
  next_invoice_number INT DEFAULT 1,
  declaration_text TEXT,
  terms_text TEXT,
  bank_details TEXT,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  address TEXT,
  phone VARCHAR(30),
  email VARCHAR(150),
  gstin VARCHAR(30),
  state_name VARCHAR(100),
  state_code VARCHAR(10),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_number VARCHAR(100) NOT NULL UNIQUE,
  invoice_date DATE NOT NULL,
  customer_id INT,
  customer_name VARCHAR(150) NOT NULL,
  customer_address TEXT,
  customer_phone VARCHAR(30),
  customer_email VARCHAR(150),
  customer_gstin VARCHAR(30),
  customer_state_name VARCHAR(100),
  customer_state_code VARCHAR(10),
  consignee_name VARCHAR(150),
  consignee_address TEXT,
  consignee_gstin VARCHAR(30),
  consignee_state_name VARCHAR(100),
  consignee_state_code VARCHAR(10),
  payment_mode VARCHAR(50),
  delivery_note VARCHAR(100),
  buyer_order_no VARCHAR(100),
  dispatch_doc_no VARCHAR(100),
  delivery_date DATE,
  destination VARCHAR(150),
  terms_of_delivery TEXT,
  taxable_amount DECIMAL(12,2) DEFAULT 0,
  cgst_amount DECIMAL(12,2) DEFAULT 0,
  sgst_amount DECIMAL(12,2) DEFAULT 0,
  igst_amount DECIMAL(12,2) DEFAULT 0,
  round_off DECIMAL(12,2) DEFAULT 0,
  grand_total DECIMAL(12,2) DEFAULT 0,
  amount_in_words TEXT,
  show_logo BOOLEAN DEFAULT TRUE,
  show_signature BOOLEAN DEFAULT TRUE,
  show_seal BOOLEAN DEFAULT TRUE,
  status ENUM('draft','active','cancelled') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS invoice_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT NOT NULL,
  description VARCHAR(255) NOT NULL,
  paper_type ENUM('Soft','Semi Soft','Hard') NOT NULL,
  hsn_code VARCHAR(20),
  quantity DECIMAL(12,3) NOT NULL,
  rate DECIMAL(12,2) NOT NULL,
  discount_percent DECIMAL(5,2) DEFAULT 0,
  taxable_amount DECIMAL(12,2) NOT NULL,
  cgst_percent DECIMAL(5,2) DEFAULT 0,
  cgst_amount DECIMAL(12,2) DEFAULT 0,
  sgst_percent DECIMAL(5,2) DEFAULT 0,
  sgst_amount DECIMAL(12,2) DEFAULT 0,
  igst_percent DECIMAL(5,2) DEFAULT 0,
  igst_amount DECIMAL(12,2) DEFAULT 0,
  total_amount DECIMAL(12,2) NOT NULL,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB;
