-- UOMS ERP Complete Database Setup
CREATE DATABASE IF NOT EXISTS uoms_settings CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS uoms_accounting CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS uoms_employee CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS uoms_inventory CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS uoms_sales CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS uoms_purchasing CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS uoms_projects CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS uoms_compliance CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS uoms_document CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS uoms_quotations CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS uoms_tasks CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS uoms_crm CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS uoms_collaboration CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS uoms_pos CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


GRANT ALL PRIVILEGES ON uoms_*.* TO 'uoms_user'@'localhost';
FLUSH PRIVILEGES;

USE uoms_settings;
CREATE TABLE users (id CHAR(36) PRIMARY KEY, tenant_id CHAR(36) NOT NULL, email VARCHAR(255) UNIQUE, username VARCHAR(100) UNIQUE, password VARCHAR(255), first_name VARCHAR(100), last_name VARCHAR(100), is_active BOOLEAN DEFAULT true, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, INDEX idx_tenant (tenant_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE roles (id CHAR(36) PRIMARY KEY, tenant_id CHAR(36) NOT NULL, name VARCHAR(100) UNIQUE, permissions JSON, is_active BOOLEAN DEFAULT true, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, INDEX idx_tenant (tenant_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE settings (id CHAR(36) PRIMARY KEY, tenant_id CHAR(36) NOT NULL, key VARCHAR(255), value LONGTEXT, type VARCHAR(50), is_encrypted BOOLEAN DEFAULT false, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, UNIQUE KEY uk_tenant_key (tenant_id, key), INDEX idx_tenant (tenant_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

USE uoms_accounting;
CREATE TABLE accounts (id CHAR(36) PRIMARY KEY, tenant_id CHAR(36) NOT NULL, code VARCHAR(50) UNIQUE, name VARCHAR(255), type ENUM('asset','liability','equity','revenue','expense'), balance DECIMAL(15,2) DEFAULT 0, is_active BOOLEAN DEFAULT true, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, INDEX idx_tenant (tenant_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

USE uoms_employee;
CREATE TABLE employees (id CHAR(36) PRIMARY KEY, tenant_id CHAR(36) NOT NULL, emp_code VARCHAR(50) UNIQUE, first_name VARCHAR(100), last_name VARCHAR(100), email VARCHAR(255), phone VARCHAR(20), national_id VARCHAR(50), dob DATE, gender VARCHAR(10), designation VARCHAR(100), hire_date DATE, status VARCHAR(20), basic_salary DECIMAL(15,2), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, INDEX idx_tenant (tenant_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

USE uoms_inventory;
CREATE TABLE products (id CHAR(36) PRIMARY KEY, tenant_id CHAR(36) NOT NULL, sku VARCHAR(50) UNIQUE, name VARCHAR(255), description TEXT, cost_price DECIMAL(15,2), selling_price DECIMAL(15,2), reorder_level INT, is_active BOOLEAN DEFAULT true, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, INDEX idx_tenant (tenant_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE inventory (id CHAR(36) PRIMARY KEY, tenant_id CHAR(36) NOT NULL, product_id CHAR(36), warehouse_id CHAR(36), quantity INT DEFAULT 0, reorder_level INT, status VARCHAR(20), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, INDEX idx_tenant (tenant_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

USE uoms_sales;
CREATE TABLE sales (id CHAR(36) PRIMARY KEY, tenant_id CHAR(36) NOT NULL, sale_no VARCHAR(50) UNIQUE, customer_id CHAR(36), sale_date DATE, total_amount DECIMAL(15,2), status VARCHAR(20), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, INDEX idx_tenant (tenant_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE customers (id CHAR(36) PRIMARY KEY, tenant_id CHAR(36) NOT NULL, name VARCHAR(255), email VARCHAR(255), phone VARCHAR(20), address TEXT, city VARCHAR(100), is_active BOOLEAN DEFAULT true, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, INDEX idx_tenant (tenant_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

USE uoms_purchasing;
CREATE TABLE purchase_orders (id CHAR(36) PRIMARY KEY, tenant_id CHAR(36) NOT NULL, po_no VARCHAR(50) UNIQUE, supplier_id CHAR(36), order_date DATE, expected_date DATE, total_amount DECIMAL(15,2), status VARCHAR(20), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, INDEX idx_tenant (tenant_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE suppliers (id CHAR(36) PRIMARY KEY, tenant_id CHAR(36) NOT NULL, name VARCHAR(255), email VARCHAR(255), phone VARCHAR(20), address TEXT, city VARCHAR(100), is_active BOOLEAN DEFAULT true, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, INDEX idx_tenant (tenant_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

USE uoms_projects;
CREATE TABLE projects (id CHAR(36) PRIMARY KEY, tenant_id CHAR(36) NOT NULL, name VARCHAR(255), description TEXT, start_date DATE, end_date DATE, status VARCHAR(20), budget DECIMAL(15,2), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, INDEX idx_tenant (tenant_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

USE uoms_compliance;
CREATE TABLE tickets (id CHAR(36) PRIMARY KEY, tenant_id CHAR(36) NOT NULL, ticket_no VARCHAR(50) UNIQUE, subject VARCHAR(255), description TEXT, priority VARCHAR(20), status VARCHAR(20), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, INDEX idx_tenant (tenant_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

USE uoms_document;
CREATE TABLE documents (id CHAR(36) PRIMARY KEY, tenant_id CHAR(36) NOT NULL, name VARCHAR(255), file_path VARCHAR(255), file_size INT, file_type VARCHAR(50), status VARCHAR(20), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, INDEX idx_tenant (tenant_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

USE uoms_quotations;
CREATE TABLE quotes (id CHAR(36) PRIMARY KEY, tenant_id CHAR(36) NOT NULL, quote_no VARCHAR(50) UNIQUE, customer_id CHAR(36), quote_date DATE, valid_until DATE, total_amount DECIMAL(15,2), status VARCHAR(20), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, INDEX idx_tenant (tenant_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

USE uoms_tasks;
CREATE TABLE tasks (id CHAR(36) PRIMARY KEY, tenant_id CHAR(36) NOT NULL, title VARCHAR(255), description TEXT, assigned_to CHAR(36), status VARCHAR(20), priority VARCHAR(20), due_date DATE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, INDEX idx_tenant (tenant_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

USE uoms_crm;
CREATE TABLE leads (id CHAR(36) PRIMARY KEY, tenant_id CHAR(36) NOT NULL, name VARCHAR(255), email VARCHAR(255), phone VARCHAR(20), company VARCHAR(255), status VARCHAR(20), source VARCHAR(50), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, INDEX idx_tenant (tenant_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

USE uoms_collaboration;
CREATE TABLE chat_rooms (id CHAR(36) PRIMARY KEY, tenant_id CHAR(36) NOT NULL, name VARCHAR(255), description TEXT, is_private BOOLEAN DEFAULT false, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, INDEX idx_tenant (tenant_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE calendar_events (id CHAR(36) PRIMARY KEY, tenant_id CHAR(36) NOT NULL, title VARCHAR(255), description TEXT, start_date DATETIME, end_date DATETIME, location VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, INDEX idx_tenant (tenant_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

USE uoms_pos;
CREATE TABLE pos_terminals (id CHAR(36) PRIMARY KEY, tenant_id CHAR(36) NOT NULL, terminal_name VARCHAR(255), location VARCHAR(255), is_active BOOLEAN DEFAULT true, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, INDEX idx_tenant (tenant_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
