-- =====================================================================
--  LedgerPro Accounting System - database schema
--  Import with:  mysql -u root -p < schema.sql
--  Default login after import:  admin / admin123   (change it immediately)
-- =====================================================================

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

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS notification_reads, notifications, payments, receipts, bill_items, bills, invoice_items, invoices, suppliers, customers, users;
SET FOREIGN_KEY_CHECKS = 1;

-- ---------------------------------------------------------------- users
CREATE TABLE users (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(120) NOT NULL,
  username      VARCHAR(50)  NOT NULL UNIQUE,
  email         VARCHAR(150) NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role          ENUM('admin','accountant','cashier','viewer') NOT NULL DEFAULT 'viewer',
  status        ENUM('active','inactive') NOT NULL DEFAULT 'active',
  last_login    DATETIME NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------ customers (debtors)
CREATE TABLE customers (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code            VARCHAR(30)  NOT NULL UNIQUE,
  name            VARCHAR(150) NOT NULL,
  contact_person  VARCHAR(120) NULL,
  email           VARCHAR(150) NULL,
  phone           VARCHAR(40)  NULL,
  address         TEXT NULL,
  credit_limit    DECIMAL(14,2) NOT NULL DEFAULT 0,
  credit_days     SMALLINT UNSIGNED NOT NULL DEFAULT 30,
  opening_balance DECIMAL(14,2) NOT NULL DEFAULT 0,
  status          ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_customers_name (name)
) ENGINE=InnoDB;

-- ------------------------------------------------ suppliers (creditors)
CREATE TABLE suppliers (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code            VARCHAR(30)  NOT NULL UNIQUE,
  name            VARCHAR(150) NOT NULL,
  contact_person  VARCHAR(120) NULL,
  email           VARCHAR(150) NULL,
  phone           VARCHAR(40)  NULL,
  address         TEXT NULL,
  credit_limit    DECIMAL(14,2) NOT NULL DEFAULT 0,
  credit_days     SMALLINT UNSIGNED NOT NULL DEFAULT 30,
  opening_balance DECIMAL(14,2) NOT NULL DEFAULT 0,
  status          ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_suppliers_name (name)
) ENGINE=InnoDB;

-- ------------------------------------------- sales invoices (to debtors)
CREATE TABLE invoices (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_no       VARCHAR(30) NOT NULL UNIQUE,
  customer_id      INT UNSIGNED NOT NULL,
  invoice_date     DATE NOT NULL,
  due_date         DATE NOT NULL,
  reference        VARCHAR(100) NULL,
  subtotal         DECIMAL(14,2) NOT NULL DEFAULT 0,   -- sum of line nets (before tax)
  tax_total        DECIMAL(14,2) NOT NULL DEFAULT 0,   -- sum of line taxes
  amount           DECIMAL(14,2) NOT NULL,             -- grand total = subtotal + tax_total
  paid_amount      DECIMAL(14,2) NOT NULL DEFAULT 0,
  status           ENUM('unpaid','partial','paid','cancelled') NOT NULL DEFAULT 'unpaid',
  notes            TEXT NULL,
  last_reminder_at DATETIME NULL,
  reminder_count   INT UNSIGNED NOT NULL DEFAULT 0,
  created_by       INT UNSIGNED NULL,
  created_at       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_invoices_status_due (status, due_date),
  INDEX idx_invoices_customer (customer_id),
  CONSTRAINT fk_inv_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT,
  CONSTRAINT fk_inv_user     FOREIGN KEY (created_by)  REFERENCES users(id)     ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------- invoice line items
CREATE TABLE invoice_items (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_id  INT UNSIGNED NOT NULL,
  line_no     SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  description VARCHAR(255) NOT NULL,
  quantity    DECIMAL(12,3) NOT NULL DEFAULT 1,
  unit_price  DECIMAL(14,2) NOT NULL DEFAULT 0,
  tax_rate    DECIMAL(5,2)  NOT NULL DEFAULT 0,        -- percent, e.g. 10.00
  line_net    DECIMAL(14,2) NOT NULL DEFAULT 0,        -- round(quantity * unit_price, 2)
  line_tax    DECIMAL(14,2) NOT NULL DEFAULT 0,        -- round(line_net * tax_rate / 100, 2)
  INDEX idx_items_invoice (invoice_id, line_no),
  CONSTRAINT fk_item_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------ supplier bills (payables)
CREATE TABLE bills (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  bill_no      VARCHAR(30) NOT NULL UNIQUE,
  supplier_id  INT UNSIGNED NOT NULL,
  bill_date    DATE NOT NULL,
  due_date     DATE NOT NULL,
  reference    VARCHAR(100) NULL,           -- supplier's own invoice number
  subtotal     DECIMAL(14,2) NOT NULL DEFAULT 0,   -- sum of line nets (before tax)
  tax_total    DECIMAL(14,2) NOT NULL DEFAULT 0,   -- sum of line taxes
  amount       DECIMAL(14,2) NOT NULL,             -- grand total = subtotal + tax_total
  paid_amount  DECIMAL(14,2) NOT NULL DEFAULT 0,
  status       ENUM('unpaid','partial','paid','cancelled') NOT NULL DEFAULT 'unpaid',
  notes        TEXT NULL,
  created_by   INT UNSIGNED NULL,
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_bills_status_due (status, due_date),
  INDEX idx_bills_supplier (supplier_id),
  CONSTRAINT fk_bill_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE RESTRICT,
  CONSTRAINT fk_bill_user     FOREIGN KEY (created_by)  REFERENCES users(id)     ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------- bill line items
CREATE TABLE bill_items (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  bill_id     INT UNSIGNED NOT NULL,
  line_no     SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  description VARCHAR(255) NOT NULL,
  quantity    DECIMAL(12,3) NOT NULL DEFAULT 1,
  unit_price  DECIMAL(14,2) NOT NULL DEFAULT 0,
  tax_rate    DECIMAL(5,2)  NOT NULL DEFAULT 0,
  line_net    DECIMAL(14,2) NOT NULL DEFAULT 0,
  line_tax    DECIMAL(14,2) NOT NULL DEFAULT 0,
  INDEX idx_bill_items_bill (bill_id, line_no),
  CONSTRAINT fk_bitem_bill FOREIGN KEY (bill_id) REFERENCES bills(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------- received payments (from debtors) + cheques
CREATE TABLE receipts (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  receipt_no    VARCHAR(30) NOT NULL UNIQUE,
  customer_id   INT UNSIGNED NOT NULL,
  invoice_id    INT UNSIGNED NULL,            -- NULL = payment on account
  receipt_date  DATE NOT NULL,
  amount        DECIMAL(14,2) NOT NULL,
  method        ENUM('cash','cheque','bank_transfer','card') NOT NULL DEFAULT 'cash',
  cheque_no     VARCHAR(40) NULL,
  cheque_date   DATE NULL,
  bank_name     VARCHAR(100) NULL,
  cheque_status ENUM('pending','cleared','bounced') NULL,  -- only for cheques
  reference     VARCHAR(100) NULL,
  notes         TEXT NULL,
  created_by    INT UNSIGNED NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_receipts_date (receipt_date),
  INDEX idx_receipts_customer (customer_id),
  INDEX idx_receipts_cheque (method, cheque_status, cheque_date),
  CONSTRAINT fk_rec_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT,
  CONSTRAINT fk_rec_invoice  FOREIGN KEY (invoice_id)  REFERENCES invoices(id)  ON DELETE RESTRICT,
  CONSTRAINT fk_rec_user     FOREIGN KEY (created_by)  REFERENCES users(id)     ON DELETE SET NULL
) ENGINE=InnoDB;

-- ----------------------------- payments to suppliers (incl. cheques)
CREATE TABLE payments (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payment_no    VARCHAR(30) NOT NULL UNIQUE,
  supplier_id   INT UNSIGNED NOT NULL,
  bill_id       INT UNSIGNED NULL,            -- NULL = advance / on account
  payment_date  DATE NOT NULL,
  amount        DECIMAL(14,2) NOT NULL,
  method        ENUM('cash','cheque','bank_transfer','card') NOT NULL DEFAULT 'cash',
  cheque_no     VARCHAR(40) NULL,
  cheque_date   DATE NULL,
  bank_name     VARCHAR(100) NULL,
  cheque_status ENUM('pending','cleared','bounced') NULL,
  reference     VARCHAR(100) NULL,
  notes         TEXT NULL,
  created_by    INT UNSIGNED NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_payments_date (payment_date),
  INDEX idx_payments_supplier (supplier_id),
  INDEX idx_payments_cheque (method, cheque_status, cheque_date),
  CONSTRAINT fk_pay_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE RESTRICT,
  CONSTRAINT fk_pay_bill     FOREIGN KEY (bill_id)     REFERENCES bills(id)     ON DELETE RESTRICT,
  CONSTRAINT fk_pay_user     FOREIGN KEY (created_by)  REFERENCES users(id)     ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------- notifications
CREATE TABLE notifications (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uniq_key   VARCHAR(120) NOT NULL UNIQUE,    -- prevents duplicate alerts
  type       ENUM('info','success','warning','danger') NOT NULL DEFAULT 'info',
  title      VARCHAR(150) NOT NULL,
  message    VARCHAR(500) NOT NULL,
  link       VARCHAR(200) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE notification_reads (
  user_id         INT UNSIGNED NOT NULL,
  notification_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (user_id, notification_id),
  CONSTRAINT fk_nr_user FOREIGN KEY (user_id)         REFERENCES users(id)         ON DELETE CASCADE,
  CONSTRAINT fk_nr_note FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------- default admin
INSERT INTO users (name, username, email, password_hash, role, status)
VALUES ('Administrator', 'admin', 'admin@example.com',
        '$2y$10$sVlZqriAhvUwBBPF6.iq3.n7oT27jMk1Q8Hmq6y1VsVJovrzV4qwG', 'admin', 'active');

-- ------------------------------------------------------------ company profile
CREATE TABLE IF NOT EXISTS company_profile (
  id TINYINT UNSIGNED NOT NULL PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  tagline VARCHAR(190) NULL,
  address TEXT NULL,
  phone VARCHAR(60) NULL,
  email VARCHAR(150) NULL,
  website VARCHAR(150) NULL,
  tax_no VARCHAR(60) NULL,
  reg_no VARCHAR(60) NULL,
  bank_details TEXT NULL,
  invoice_footer VARCHAR(255) NULL,
  logo VARCHAR(120) NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO company_profile (id, name, address, phone, email, invoice_footer)
VALUES (1, 'Your Company Name', '123 Main Street\nCity, Country', '+00 000 000 000', 'accounts@example.com', 'Thank you for your business.');

-- ------------------------------------------------------------------ audit log
CREATE TABLE IF NOT EXISTS audit_log (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  user_id INT NULL,
  username VARCHAR(60) NOT NULL DEFAULT '',
  user_role VARCHAR(20) NOT NULL DEFAULT '',
  action VARCHAR(30) NOT NULL,
  entity VARCHAR(30) NOT NULL,
  entity_id INT NULL,
  entity_label VARCHAR(190) NOT NULL DEFAULT '',
  summary VARCHAR(500) NOT NULL DEFAULT '',
  old_values MEDIUMTEXT NULL,
  new_values MEDIUMTEXT NULL,
  ip VARCHAR(45) NOT NULL DEFAULT '',
  KEY idx_created (created_at),
  KEY idx_user (user_id, created_at),
  KEY idx_entity (entity, entity_id),
  KEY idx_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;