CREATE DATABASE IF NOT EXISTS atlas_erp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE atlas_erp;
CREATE TABLE IF NOT EXISTS users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(140) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL, role ENUM('admin','finance','sales','inventory','viewer') NOT NULL DEFAULT 'viewer', active TINYINT(1) NOT NULL DEFAULT 1,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NULL, action VARCHAR(80) NOT NULL, entity VARCHAR(80) NOT NULL,
 entity_id BIGINT NULL, details JSON NULL, ip_address VARCHAR(45) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(created_at), FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS company_settings (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_name VARCHAR(190) NOT NULL DEFAULT 'Your Company', base_currency CHAR(3) NOT NULL DEFAULT 'KES', tax_pin VARCHAR(80) NULL, address TEXT NULL, email VARCHAR(190) NULL, phone VARCHAR(50) NULL
);
CREATE TABLE IF NOT EXISTS currencies (
 code CHAR(3) PRIMARY KEY, name VARCHAR(80) NOT NULL, symbol VARCHAR(8) NOT NULL, is_crypto TINYINT(1) NOT NULL DEFAULT 0, active TINYINT(1) NOT NULL DEFAULT 1
);
INSERT IGNORE INTO currencies(code,name,symbol,is_crypto) VALUES ('KES','Kenyan Shilling','KSh',0),('USD','US Dollar','$',0),('EUR','Euro','€',0),('GBP','British Pound','£',0),('CNY','Chinese Yuan','¥',0),('AED','UAE Dirham','د.إ',0),('BTC','Bitcoin','BTC',1),('ETH','Ether','ETH',1),('USDT','Tether','USDT',1),('USDC','USD Coin','USDC',1);
CREATE TABLE IF NOT EXISTS exchange_rates (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, base_code CHAR(3) NOT NULL, quote_code CHAR(3) NOT NULL, rate DECIMAL(24,10) NOT NULL,
 effective_at DATETIME NOT NULL, source VARCHAR(120) NOT NULL DEFAULT 'manual', created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(base_code,quote_code,effective_at), FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS contacts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, kind ENUM('customer','supplier','both') NOT NULL, name VARCHAR(190) NOT NULL, email VARCHAR(190) NULL, phone VARCHAR(60) NULL,
 tax_id VARCHAR(100) NULL, address TEXT NULL, currency CHAR(3) NOT NULL DEFAULT 'KES', notes TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX(kind,name)
);
CREATE TABLE IF NOT EXISTS products (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, sku VARCHAR(100) UNIQUE, name VARCHAR(190) NOT NULL, category VARCHAR(120) NULL, unit VARCHAR(30) NOT NULL DEFAULT 'pcs',
 cost_price DECIMAL(20,4) NOT NULL DEFAULT 0, selling_price DECIMAL(20,4) NOT NULL DEFAULT 0, currency CHAR(3) NOT NULL DEFAULT 'KES', stock_qty DECIMAL(20,4) NOT NULL DEFAULT 0,
 reorder_level DECIMAL(20,4) NOT NULL DEFAULT 0, active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS documents (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, doc_type ENUM('invoice','purchase_order','expense','credit_note','quote') NOT NULL, doc_number VARCHAR(100) NOT NULL UNIQUE,
 contact_id BIGINT UNSIGNED NULL, issue_date DATE NOT NULL, due_date DATE NULL, currency CHAR(3) NOT NULL DEFAULT 'KES', exchange_rate DECIMAL(24,10) NOT NULL DEFAULT 1,
 subtotal DECIMAL(20,4) NOT NULL DEFAULT 0, tax_amount DECIMAL(20,4) NOT NULL DEFAULT 0, total DECIMAL(20,4) NOT NULL DEFAULT 0,
 status ENUM('draft','sent','approved','part_paid','paid','void','overdue') NOT NULL DEFAULT 'draft', notes TEXT NULL, created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(doc_type,status,issue_date), FOREIGN KEY(contact_id) REFERENCES contacts(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS document_lines (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, document_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NULL, description VARCHAR(255) NOT NULL,
 quantity DECIMAL(20,4) NOT NULL DEFAULT 1, unit_price DECIMAL(20,4) NOT NULL DEFAULT 0, tax_rate DECIMAL(8,4) NOT NULL DEFAULT 0, line_total DECIMAL(20,4) NOT NULL DEFAULT 0,
 FOREIGN KEY(document_id) REFERENCES documents(id) ON DELETE CASCADE, FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS payments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, document_id BIGINT UNSIGNED NULL, direction ENUM('in','out') NOT NULL DEFAULT 'in', method ENUM('bank','cash','mpesa','card','crypto','other') NOT NULL,
 currency CHAR(3) NOT NULL DEFAULT 'KES', amount DECIMAL(24,10) NOT NULL, base_amount DECIMAL(20,4) NOT NULL DEFAULT 0, exchange_rate DECIMAL(24,10) NOT NULL DEFAULT 1,
 provider VARCHAR(100) NULL, provider_reference VARCHAR(190) NULL, crypto_asset VARCHAR(20) NULL, network VARCHAR(60) NULL, wallet_address VARCHAR(255) NULL,
 tx_hash VARCHAR(255) NULL, status ENUM('pending','confirmed','failed','cancelled','refunded','reconciled') NOT NULL DEFAULT 'pending', received_at DATETIME NULL,
 created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX(status,created_at), INDEX(tx_hash),
 FOREIGN KEY(document_id) REFERENCES documents(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS expenses (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, expense_date DATE NOT NULL, category VARCHAR(120) NOT NULL, description VARCHAR(255) NOT NULL, supplier_id BIGINT UNSIGNED NULL,
 currency CHAR(3) NOT NULL DEFAULT 'KES', amount DECIMAL(20,4) NOT NULL, tax_amount DECIMAL(20,4) NOT NULL DEFAULT 0, exchange_rate DECIMAL(24,10) NOT NULL DEFAULT 1,
 status ENUM('pending','approved','rejected','paid') NOT NULL DEFAULT 'pending', created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(supplier_id) REFERENCES contacts(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS stock_movements (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, product_id BIGINT UNSIGNED NOT NULL, movement_type ENUM('opening','purchase','sale','adjustment','return','transfer') NOT NULL,
 quantity DECIMAL(20,4) NOT NULL, reference VARCHAR(150) NULL, notes TEXT NULL, created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
);