-- Accounting System - MySQL Schema
-- Run this after creating database: CREATE DATABASE accounting2;


-- Core setup
CREATE TABLE IF NOT EXISTS `companies` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `address` TEXT,
  `tax_id` VARCHAR(50),
  `phone` VARCHAR(50),
  `email` VARCHAR(100),
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_companies_active` (`is_active`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `financial_years` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  `start_date` DATE NOT NULL,
  `end_date` DATE NOT NULL,
  `is_closed` TINYINT(1) DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_fy_company` (`company_id`),
  INDEX `idx_fy_dates` (`start_date`, `end_date`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `period_locks` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `financial_year_id` INT UNSIGNED NOT NULL,
  `lock_date` DATE NOT NULL,
  `created_by` INT UNSIGNED,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_fy_lock` (`financial_year_id`, `lock_date`),
  FOREIGN KEY (`financial_year_id`) REFERENCES `financial_years`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `roles` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `description` VARCHAR(255),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_roles_name` (`name`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `permissions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(80) NOT NULL,
  `description` VARCHAR(255),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_permissions_code` (`code`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `role_permissions` (
  `role_id` INT UNSIGNED NOT NULL,
  `permission_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`role_id`, `permission_id`),
  FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `user_roles` (
  `user_id` INT UNSIGNED NOT NULL,
  `role_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`user_id`, `role_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED,
  `action` VARCHAR(50) NOT NULL,
  `table_name` VARCHAR(80),
  `record_id` INT UNSIGNED,
  `before_data` JSON,
  `after_data` JSON,
  `ip_address` VARCHAR(45),
  `user_agent` VARCHAR(500),
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_audit_user` (`user_id`),
  INDEX `idx_audit_table` (`table_name`, `record_id`),
  INDEX `idx_audit_created` (`created_at`)
) ENGINE=InnoDB;

-- COA
CREATE TABLE IF NOT EXISTS `account_groups` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` INT UNSIGNED NOT NULL,
  `parent_id` INT UNSIGNED DEFAULT NULL,
  `name` VARCHAR(100) NOT NULL,
  `nature` ENUM('ASSET','LIABILITY','EQUITY','INCOME','EXPENSE') NOT NULL,
  `sort_order` INT DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_ag_company` (`company_id`),
  INDEX `idx_ag_parent` (`parent_id`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `ledgers` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` INT UNSIGNED NOT NULL,
  `group_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `code` VARCHAR(20),
  `ledger_type` ENUM('GENERAL','CUSTOMER','SUPPLIER','BANK','CASH','TAX') DEFAULT 'GENERAL',
  `opening_balance_dc` ENUM('D','C') DEFAULT 'D',
  `opening_balance` DECIMAL(18,2) DEFAULT 0,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_ledgers_company` (`company_id`),
  INDEX `idx_ledgers_group` (`group_id`),
  INDEX `idx_ledgers_active` (`company_id`, `is_active`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`group_id`) REFERENCES `account_groups`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `ledger_openings` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ledger_id` INT UNSIGNED NOT NULL,
  `financial_year_id` INT UNSIGNED NOT NULL,
  `opening_debit` DECIMAL(18,2) DEFAULT 0,
  `opening_credit` DECIMAL(18,2) DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_ledger_fy` (`ledger_id`, `financial_year_id`),
  FOREIGN KEY (`ledger_id`) REFERENCES `ledgers`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`financial_year_id`) REFERENCES `financial_years`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Vouchers
CREATE TABLE IF NOT EXISTS `voucher_sequences` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` INT UNSIGNED NOT NULL,
  `financial_year_id` INT UNSIGNED NOT NULL,
  `voucher_type` VARCHAR(30) NOT NULL,
  `last_number` INT UNSIGNED DEFAULT 0,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_vseq` (`company_id`, `financial_year_id`, `voucher_type`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`financial_year_id`) REFERENCES `financial_years`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `vouchers` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` INT UNSIGNED NOT NULL,
  `financial_year_id` INT UNSIGNED NOT NULL,
  `voucher_no` VARCHAR(50) NOT NULL,
  `voucher_type` VARCHAR(30) NOT NULL,
  `voucher_date` DATE NOT NULL,
  `narration` TEXT,
  `ref_module` VARCHAR(50),
  `ref_id` INT UNSIGNED,
  `is_posted` TINYINT(1) DEFAULT 1,
  `is_deleted` TINYINT(1) DEFAULT 0,
  `void_reason` TEXT,
  `posted_by` INT UNSIGNED,
  `posted_at` DATETIME,
  `created_by` INT UNSIGNED,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_vouchers_list` (`company_id`, `financial_year_id`, `voucher_date`, `voucher_type`),
  INDEX `idx_vouchers_ref` (`ref_module`, `ref_id`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`financial_year_id`) REFERENCES `financial_years`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `voucher_lines` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `voucher_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `ledger_id` INT UNSIGNED NOT NULL,
  `debit` DECIMAL(18,2) DEFAULT 0,
  `credit` DECIMAL(18,2) DEFAULT 0,
  `narration` VARCHAR(500),
  `sort_order` INT DEFAULT 0,
  PRIMARY KEY (`id`),
  INDEX `idx_vl_voucher` (`voucher_id`),
  INDEX `idx_vl_ledger` (`company_id`, `ledger_id`),
  FOREIGN KEY (`voucher_id`) REFERENCES `vouchers`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`ledger_id`) REFERENCES `ledgers`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- Sales
CREATE TABLE IF NOT EXISTS `sales_invoices` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` INT UNSIGNED NOT NULL,
  `financial_year_id` INT UNSIGNED NOT NULL,
  `invoice_no` VARCHAR(50) NOT NULL,
  `invoice_date` DATE NOT NULL,
  `customer_ledger_id` INT UNSIGNED NOT NULL,
  `subtotal` DECIMAL(18,2) DEFAULT 0,
  `discount_amount` DECIMAL(18,2) DEFAULT 0,
  `charges_amount` DECIMAL(18,2) DEFAULT 0,
  `tax_amount` DECIMAL(18,2) DEFAULT 0,
  `grand_total` DECIMAL(18,2) NOT NULL,
  `narration` TEXT,
  `voucher_id` INT UNSIGNED,
  `status` ENUM('DRAFT','POSTED','CANCELLED') DEFAULT 'DRAFT',
  `created_by` INT UNSIGNED,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_si_company_date` (`company_id`, `invoice_date`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`customer_ledger_id`) REFERENCES `ledgers`(`id`),
  FOREIGN KEY (`voucher_id`) REFERENCES `vouchers`(`id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `sales_invoice_lines` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `sales_invoice_id` INT UNSIGNED NOT NULL,
  `description` VARCHAR(500),
  `quantity` DECIMAL(18,4) DEFAULT 1,
  `rate` DECIMAL(18,4) NOT NULL,
  `amount` DECIMAL(18,2) NOT NULL,
  `ledger_id` INT UNSIGNED,
  `sort_order` INT DEFAULT 0,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`sales_invoice_id`) REFERENCES `sales_invoices`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`ledger_id`) REFERENCES `ledgers`(`id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `sales_invoice_taxes` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `sales_invoice_id` INT UNSIGNED NOT NULL,
  `tax_component_id` INT UNSIGNED,
  `taxable_amount` DECIMAL(18,2) NOT NULL,
  `rate` DECIMAL(8,4) NOT NULL,
  `amount` DECIMAL(18,2) NOT NULL,
  `ledger_id` INT UNSIGNED,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`sales_invoice_id`) REFERENCES `sales_invoices`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Purchase
CREATE TABLE IF NOT EXISTS `purchase_bills` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` INT UNSIGNED NOT NULL,
  `financial_year_id` INT UNSIGNED NOT NULL,
  `bill_no` VARCHAR(50) NOT NULL,
  `bill_date` DATE NOT NULL,
  `supplier_ledger_id` INT UNSIGNED NOT NULL,
  `subtotal` DECIMAL(18,2) DEFAULT 0,
  `discount_amount` DECIMAL(18,2) DEFAULT 0,
  `charges_amount` DECIMAL(18,2) DEFAULT 0,
  `tax_amount` DECIMAL(18,2) DEFAULT 0,
  `grand_total` DECIMAL(18,2) NOT NULL,
  `narration` TEXT,
  `voucher_id` INT UNSIGNED,
  `status` ENUM('DRAFT','POSTED','CANCELLED') DEFAULT 'DRAFT',
  `created_by` INT UNSIGNED,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_pb_company_date` (`company_id`, `bill_date`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`supplier_ledger_id`) REFERENCES `ledgers`(`id`),
  FOREIGN KEY (`voucher_id`) REFERENCES `vouchers`(`id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `purchase_bill_lines` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `purchase_bill_id` INT UNSIGNED NOT NULL,
  `description` VARCHAR(500),
  `quantity` DECIMAL(18,4) DEFAULT 1,
  `rate` DECIMAL(18,4) NOT NULL,
  `amount` DECIMAL(18,2) NOT NULL,
  `ledger_id` INT UNSIGNED,
  `sort_order` INT DEFAULT 0,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`purchase_bill_id`) REFERENCES `purchase_bills`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`ledger_id`) REFERENCES `ledgers`(`id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `purchase_bill_taxes` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `purchase_bill_id` INT UNSIGNED NOT NULL,
  `tax_component_id` INT UNSIGNED,
  `taxable_amount` DECIMAL(18,2) NOT NULL,
  `rate` DECIMAL(8,4) NOT NULL,
  `amount` DECIMAL(18,2) NOT NULL,
  `ledger_id` INT UNSIGNED,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`purchase_bill_id`) REFERENCES `purchase_bills`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Tax
CREATE TABLE IF NOT EXISTS `tax_components` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `ledger_id` INT UNSIGNED,
  `tax_type` ENUM('INPUT','OUTPUT') DEFAULT 'OUTPUT',
  `is_active` TINYINT(1) DEFAULT 1,
  PRIMARY KEY (`id`),
  INDEX `idx_tc_company` (`company_id`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`ledger_id`) REFERENCES `ledgers`(`id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `tax_rates` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tax_component_id` INT UNSIGNED NOT NULL,
  `rate` DECIMAL(8,4) NOT NULL,
  `effective_from` DATE NOT NULL,
  `effective_to` DATE,
  PRIMARY KEY (`id`),
  INDEX `idx_tr_component` (`tax_component_id`),
  FOREIGN KEY (`tax_component_id`) REFERENCES `tax_components`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Outstanding
CREATE TABLE IF NOT EXISTS `open_refs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` INT UNSIGNED NOT NULL,
  `ref_type` ENUM('AR_INVOICE','AR_RECEIPT','AP_BILL','AP_PAYMENT') NOT NULL,
  `ref_module` VARCHAR(30) NOT NULL,
  `ref_id` INT UNSIGNED NOT NULL,
  `party_ledger_id` INT UNSIGNED NOT NULL,
  `ref_date` DATE NOT NULL,
  `ref_number` VARCHAR(50),
  `ref_amount` DECIMAL(18,2) NOT NULL,
  `open_amount` DECIMAL(18,2) NOT NULL,
  `currency` VARCHAR(3) DEFAULT 'INR',
  `status` ENUM('OPEN','CLOSED','PARTIAL') DEFAULT 'OPEN',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_openrefs_lookup` (`company_id`, `party_ledger_id`, `status`, `ref_date`),
  INDEX `idx_openrefs_ref` (`ref_module`, `ref_id`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`party_ledger_id`) REFERENCES `ledgers`(`id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `ref_allocations` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `open_ref_id` INT UNSIGNED NOT NULL,
  `allocation_ref_id` INT UNSIGNED,
  `amount` DECIMAL(18,2) NOT NULL,
  `voucher_id` INT UNSIGNED,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_ra_openref` (`open_ref_id`),
  FOREIGN KEY (`open_ref_id`) REFERENCES `open_refs`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Bank reconciliation
CREATE TABLE IF NOT EXISTS `bank_statements` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` INT UNSIGNED NOT NULL,
  `ledger_id` INT UNSIGNED NOT NULL,
  `statement_date` DATE NOT NULL,
  `particulars` VARCHAR(255),
  `debit` DECIMAL(18,2) DEFAULT 0,
  `credit` DECIMAL(18,2) DEFAULT 0,
  `balance` DECIMAL(18,2),
  `import_batch` VARCHAR(50),
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_bs_ledger` (`ledger_id`, `statement_date`),
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`ledger_id`) REFERENCES `ledgers`(`id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `bank_matches` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `bank_statement_id` INT UNSIGNED NOT NULL,
  `voucher_line_id` INT UNSIGNED NOT NULL,
  `matched_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `matched_by` INT UNSIGNED,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_bs_vl` (`bank_statement_id`, `voucher_line_id`),
  FOREIGN KEY (`bank_statement_id`) REFERENCES `bank_statements`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`voucher_line_id`) REFERENCES `voucher_lines`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;


-- Seed default permissions and admin role
INSERT IGNORE INTO `permissions` (`code`, `description`) VALUES
('*', 'Full access'),
('company.view', 'View company and FY'),
('company.create', 'Create company and FY'),
('company.lock', 'Manage period locks'),
('admin.override_lock', 'Override period lock'),
('masters.view', 'View masters'),
('masters.create', 'Create/edit masters'),
('accounts.view', 'View vouchers'),
('accounts.create', 'Create vouchers'),
('accounts.post', 'Post vouchers'),
('accounts.void', 'Void vouchers'),
('sales.view', 'View sales'),
('sales.create', 'Create sales invoices'),
('sales.post', 'Post sales invoices'),
('sales.cancel', 'Cancel sales invoices'),
('purchase.view', 'View purchase'),
('purchase.create', 'Create purchase bills'),
('purchase.post', 'Post purchase bills'),
('purchase.cancel', 'Cancel purchase bills'),
('tax.view', 'View tax'),
('reports.view', 'View reports'),
('bank.view', 'View bank'),
('bank.reconcile', 'Bank reconciliation'),
('admin.users', 'Manage users'),
('admin.roles', 'Manage roles'),
('admin.audit', 'View audit log');

INSERT IGNORE INTO `roles` (`name`, `description`) VALUES ('Admin', 'Full system access');

-- Grant all permissions to Admin role
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p WHERE r.name = 'Admin' AND NOT EXISTS (
  SELECT 1 FROM role_permissions rp WHERE rp.role_id = r.id AND rp.permission_id = p.id
);
