-- ============================================================ -- Accounting ERP - Full Database Schema -- MySQL 8.0+ / MariaDB 10.4+ -- ============================================================ SET FOREIGN_KEY_CHECKS = 0; SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; -- ------------------------------------------------------------ -- 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; SET FOREIGN_KEY_CHECKS = 1; -- ============================================================ -- 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.