-- ============================================================
-- Accounting ERP - Full Database Schema
-- MySQL 8.0+ / MariaDB 10.4+
-- ============================================================


-- ------------------------------------------------------------
-- Create Database
-- ------------------------------------------------------------
CREATE DATABASE IF NOT EXISTS `accounting_erp`
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE `accounting_erp`;

-- ------------------------------------------------------------
-- currency
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `currency` (
    `id`          INT AUTO_INCREMENT PRIMARY KEY,
    `title`       VARCHAR(50)  NOT NULL,
    `code`        VARCHAR(10)  NOT NULL UNIQUE,
    `symbol`      VARCHAR(10)  DEFAULT '$',
    `position`    ENUM('Before','After') DEFAULT 'Before',
    `rate`        DECIMAL(18,4) DEFAULT 1.0000,
    `rate_date`   DATE         DEFAULT (CURRENT_DATE),
    `description` TEXT,
    `is_default`  TINYINT(1)   DEFAULT 0,
    `created_at`  DATETIME     DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- account_group
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `account_group` (
    `id`          INT AUTO_INCREMENT PRIMARY KEY,
    `name`        VARCHAR(100) NOT NULL,
    `type`        ENUM('Asset','Liability','Equity','Revenue','Expense') NOT NULL,
    `description` TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- business_type
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `business_type` (
    `id`   INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- country
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `country` (
    `id`   INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `code` VARCHAR(5)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- city
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `city` (
    `id`         INT AUTO_INCREMENT PRIMARY KEY,
    `name`       VARCHAR(100) NOT NULL,
    `country_id` INT,
    FOREIGN KEY (`country_id`) REFERENCES `country`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- accounts
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `accounts` (
    `id`               INT AUTO_INCREMENT PRIMARY KEY,
    `name`             VARCHAR(255) NOT NULL,
    `client_name`      VARCHAR(255),
    `email`            VARCHAR(255),
    `code`             VARCHAR(50)  UNIQUE,
    `parent_id`        INT          NULL,
    `date_created`     DATETIME     DEFAULT CURRENT_TIMESTAMP,
    `address`          TEXT,
    `phone`            VARCHAR(50),
    `telephone`        VARCHAR(50),
    `description`      TEXT,
    `bio`              TEXT,
    `comment`          TEXT,
    `black_comment`    TEXT,
    `currency_id`      INT,
    `financial_number` DECIMAL(18,2) DEFAULT 0.00,
    `opening_balance`  DECIMAL(18,2) DEFAULT 0.00,
    `account_status`   ENUM('Active','Inactive') DEFAULT 'Active',
    `city_id`          INT,
    `account_group_id` INT,
    `business_type_id` INT,
    `country_id`       INT,
    `tax_id`           VARCHAR(50),
    FOREIGN KEY (`currency_id`)      REFERENCES `currency`(`id`)       ON DELETE SET NULL,
    FOREIGN KEY (`parent_id`)        REFERENCES `accounts`(`id`)       ON DELETE SET NULL,
    FOREIGN KEY (`city_id`)          REFERENCES `city`(`id`)           ON DELETE SET NULL,
    FOREIGN KEY (`account_group_id`) REFERENCES `account_group`(`id`)  ON DELETE SET NULL,
    FOREIGN KEY (`business_type_id`) REFERENCES `business_type`(`id`)  ON DELETE SET NULL,
    FOREIGN KEY (`country_id`)       REFERENCES `country`(`id`)        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- register_type
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `register_type` (
    `id`          INT AUTO_INCREMENT PRIMARY KEY,
    `type`        VARCHAR(50)  NOT NULL,
    `description` TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- register
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `register` (
    `id`              INT AUTO_INCREMENT PRIMARY KEY,
    `description`     TEXT,
    `date_created`    DATETIME DEFAULT CURRENT_TIMESTAMP,
    `register_type_id` INT,
    `currency_id`     INT,
    `currency_rate`   DECIMAL(18,4) DEFAULT 1.0000,
    `notes`           TEXT,
    `comments`        TEXT,
    `status`          ENUM('Draft','Processed','Cancelled') DEFAULT 'Draft',
    FOREIGN KEY (`register_type_id`) REFERENCES `register_type`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`currency_id`)      REFERENCES `currency`(`id`)      ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- daily
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `daily` (
    `id`               INT AUTO_INCREMENT PRIMARY KEY,
    `account_id`       INT,
    `debit`            DECIMAL(18,2) DEFAULT 0.00,
    `credit`           DECIMAL(18,2) DEFAULT 0.00,
    `is_check`         TINYINT(1) DEFAULT 0,
    `check_number`     VARCHAR(50),
    `check_date`       DATE,
    `description`      TEXT,
    `record_id`        INT,
    `linked_invoice_id` INT,
    `created_at`       DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`record_id`)  REFERENCES `register`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- invoice_type
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `invoice_type` (
    `id`   INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- item
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `item` (
    `id`             INT AUTO_INCREMENT PRIMARY KEY,
    `name`           VARCHAR(255) NOT NULL,
    `sku`            VARCHAR(100) UNIQUE,
    `account_id`     INT,
    `price`          DECIMAL(18,2) DEFAULT 0.00,
    `cost`           DECIMAL(18,2) DEFAULT 0.00,
    `description`    TEXT,
    `quantity`       DECIMAL(18,2) DEFAULT 0.00,
    `reorder_level`  DECIMAL(18,2) DEFAULT 0.00,
    `stock_tracking` TINYINT(1) DEFAULT 1,
    `is_active`      TINYINT(1) DEFAULT 1,
    `created_at`     DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- invoice
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `invoice` (
    `id`              INT AUTO_INCREMENT PRIMARY KEY,
    `code`            VARCHAR(50),
    `account_id`      INT,
    `description`     TEXT,
    `register_id`     INT,
    `invoice_nb`      VARCHAR(50) UNIQUE,
    `invoice_type_id` INT,
    `date_created`    DATETIME DEFAULT CURRENT_TIMESTAMP,
    `due_date`        DATE,
    `subtotal`        DECIMAL(18,2) DEFAULT 0.00,
    `tax_total`       DECIMAL(18,2) DEFAULT 0.00,
    `discount_total`  DECIMAL(18,2) DEFAULT 0.00,
    `total`           DECIMAL(18,2) DEFAULT 0.00,
    `currency_id`     INT,
    `status`          ENUM('Draft','Posted','Cancelled') DEFAULT 'Draft',
    FOREIGN KEY (`account_id`)      REFERENCES `accounts`(`id`)      ON DELETE SET NULL,
    FOREIGN KEY (`register_id`)     REFERENCES `register`(`id`)      ON DELETE SET NULL,
    FOREIGN KEY (`invoice_type_id`) REFERENCES `invoice_type`(`id`)  ON DELETE SET NULL,
    FOREIGN KEY (`currency_id`)     REFERENCES `currency`(`id`)      ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- invoice_details
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `invoice_details` (
    `id`            INT AUTO_INCREMENT PRIMARY KEY,
    `invoice_id`    INT NOT NULL,
    `item_id`       INT,
    `description`   TEXT,
    `unit_price`    DECIMAL(18,2) DEFAULT 0.00,
    `quantity`      DECIMAL(18,2) DEFAULT 1.00,
    `discount`      DECIMAL(18,2) DEFAULT 0.00,
    `discount_type` ENUM('Amount','Percentage') DEFAULT 'Amount',
    `tax`           DECIMAL(18,2) DEFAULT 0.00,
    `line_total`    DECIMAL(18,2) DEFAULT 0.00,
    FOREIGN KEY (`invoice_id`) REFERENCES `invoice`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`item_id`)    REFERENCES `item`(`id`)    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- receipts
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `receipts` (
    `id`           INT AUTO_INCREMENT PRIMARY KEY,
    `register_id`  INT,
    `receipt_nb`   VARCHAR(50),
    `account_id`   INT,
    `amount`       DECIMAL(18,2) DEFAULT 0.00,
    `date_created` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `notes`        TEXT,
    FOREIGN KEY (`register_id`) REFERENCES `register`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`account_id`)  REFERENCES `accounts`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- payments
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `payments` (
    `id`           INT AUTO_INCREMENT PRIMARY KEY,
    `register_id`  INT,
    `payment_nb`   VARCHAR(50),
    `account_id`   INT,
    `amount`       DECIMAL(18,2) DEFAULT 0.00,
    `date_created` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `notes`        TEXT,
    FOREIGN KEY (`register_id`) REFERENCES `register`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`account_id`)  REFERENCES `accounts`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- sales
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sales` (
    `id`          INT AUTO_INCREMENT PRIMARY KEY,
    `register_id` INT,
    `sales_nb`    VARCHAR(50),
    FOREIGN KEY (`register_id`) REFERENCES `register`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- sales_return
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sales_return` (
    `id`          INT AUTO_INCREMENT PRIMARY KEY,
    `register_id` INT,
    FOREIGN KEY (`register_id`) REFERENCES `register`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- purchase
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `purchase` (
    `id`          INT AUTO_INCREMENT PRIMARY KEY,
    `register_id` INT,
    `invoice_nb`  VARCHAR(50),
    FOREIGN KEY (`register_id`) REFERENCES `register`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- purchase_return
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `purchase_return` (
    `id`          INT AUTO_INCREMENT PRIMARY KEY,
    `register_id` INT,
    FOREIGN KEY (`register_id`) REFERENCES `register`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- payment_due
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `payment_due` (
    `id`                INT AUTO_INCREMENT PRIMARY KEY,
    `date_create`       DATETIME DEFAULT CURRENT_TIMESTAMP,
    `due_date`          DATE,
    `amount`            DECIMAL(18,2) DEFAULT 0.00,
    `fees`              DECIMAL(18,2) DEFAULT 0.00,
    `tax`               DECIMAL(18,2) DEFAULT 0.00,
    `discount`          DECIMAL(18,2) DEFAULT 0.00,
    `description`       TEXT,
    `client_id`         INT,
    `currency_id`       INT,
    `status_id`         ENUM('Pending','Partial','Paid','Overdue') DEFAULT 'Pending',
    `receipt_id`        INT,
    `linked_payment_id` INT,
    `notes`             TEXT,
    `comments`          TEXT,
    `payment_due_nb`    VARCHAR(50) UNIQUE,
    FOREIGN KEY (`client_id`)   REFERENCES `accounts`(`id`)  ON DELETE SET NULL,
    FOREIGN KEY (`currency_id`) REFERENCES `currency`(`id`)  ON DELETE SET NULL,
    FOREIGN KEY (`receipt_id`)  REFERENCES `receipts`(`id`)  ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- users
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
    `id`         INT AUTO_INCREMENT PRIMARY KEY,
    `username`   VARCHAR(50) NOT NULL UNIQUE,
    `password`   VARCHAR(255) NOT NULL,
    `name`       VARCHAR(255),
    `email`      VARCHAR(255),
    `role`       ENUM('Admin','Accountant','Viewer') DEFAULT 'Viewer',
    `is_active`  TINYINT(1) DEFAULT 1,
    `last_login` DATETIME,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ============================================================
-- Seed Data
-- ============================================================

-- Default currency
INSERT INTO `currency` (`title`,`code`,`symbol`,`position`,`rate`,`is_default`) VALUES
('US Dollar','USD','$','Before',1.0000,1),
('Euro','EUR','€','Before',0.9200,0),
('British Pound','GBP','£','Before',0.7900,0);

-- Account groups
INSERT INTO `account_group` (`name`,`type`) VALUES
('Cash & Bank','Asset'),
('Accounts Receivable','Asset'),
('Inventory','Asset'),
('Fixed Assets','Asset'),
('Accounts Payable','Liability'),
('Long-term Debt','Liability'),
('Owner Equity','Equity'),
('Sales Revenue','Revenue'),
('Other Income','Revenue'),
('Cost of Goods Sold','Expense'),
('Operating Expenses','Expense'),
('Payroll Expenses','Expense');

-- Register types
INSERT INTO `register_type` (`type`,`description`) VALUES
('Journal Entry','General accounting journal entries'),
('Receipt','Money received from customers'),
('Payment','Money paid to vendors/suppliers'),
('Sales Invoice','Sales to customers'),
('Purchase Invoice','Purchases from suppliers'),
('Sales Return','Returned sales'),
('Purchase Return','Returned purchases');

-- Invoice types
INSERT INTO `invoice_type` (`name`) VALUES
('Sales Invoice'),
('Purchase Invoice'),
('Sales Return'),
('Purchase Return'),
('Credit Note'),
('Debit Note');

-- Countries
INSERT INTO `country` (`name`,`code`) VALUES
('United States','US'),
('United Kingdom','GB'),
('Germany','DE'),
('France','FR'),
('Lebanon','LB'),
('UAE','AE');

-- Business types
INSERT INTO `business_type` (`name`) VALUES
('Individual'),
('Company'),
('Government'),
('Non-profit'),
('Partnership');

-- Admin user
-- Default credentials: username=admin  password=Admin@123
-- After import, run:  php setup.php
-- Or manually generate hash:  php -r "echo password_hash('Admin@123', PASSWORD_BCRYPT, ['cost'=>12]);"
INSERT INTO `users` (`username`, `password`, `name`, `email`, `role`, `is_active`) VALUES (
    'admin',
    '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
    'System Administrator',
    'admin@erp.local',
    'Admin',
    1
);
-- NOTE: The hash above is a placeholder for 'password'. 
-- Run `php setup.php` from the project root to set it to 'Admin@123' correctly.
