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

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS audit_logs;
DROP TABLE IF EXISTS payrolls;
DROP TABLE IF EXISTS payroll_periods;
DROP TABLE IF EXISTS attendance;
DROP TABLE IF EXISTS cash_transactions;
DROP TABLE IF EXISTS service_assignments;
DROP TABLE IF EXISTS service_materials;
DROP TABLE IF EXISTS service_orders;
DROP TABLE IF EXISTS services;
DROP TABLE IF EXISTS shipments;
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS stores;
DROP TABLE IF EXISTS platforms;
DROP TABLE IF EXISTS sales_channels;
DROP TABLE IF EXISTS inventory_transactions;
DROP TABLE IF EXISTS inventory_balances;
DROP TABLE IF EXISTS warehouses;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS brands;
DROP TABLE IF EXISTS product_categories;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS role_permissions;
DROP TABLE IF EXISTS permissions;
DROP TABLE IF EXISTS roles;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(60) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    description TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(120) NOT NULL UNIQUE,
    name VARCHAR(120) NOT NULL,
    module_name VARCHAR(80) NOT NULL,
    action_name VARCHAR(40) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE role_permissions (
    role_id BIGINT UNSIGNED NOT NULL,
    permission_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    CONSTRAINT fk_role_permissions_role FOREIGN KEY (role_id) REFERENCES roles(id),
    CONSTRAINT fk_role_permissions_permission FOREIGN KEY (permission_id) REFERENCES permissions(id)
) ENGINE=InnoDB;

CREATE TABLE employees (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_code VARCHAR(30) NOT NULL UNIQUE,
    full_name VARCHAR(150) NOT NULL,
    division VARCHAR(80) NOT NULL,
    position_name VARCHAR(100) NOT NULL,
    manager_employee_id BIGINT UNSIGNED NULL,
    phone VARCHAR(30) NULL,
    address TEXT NULL,
    join_date DATE NULL,
    employment_status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    basic_salary DECIMAL(15,2) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_employees_manager FOREIGN KEY (manager_employee_id) REFERENCES employees(id)
) ENGINE=InnoDB;

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(80) NOT NULL UNIQUE,
    email VARCHAR(150) NULL UNIQUE,
    profile_photo VARCHAR(255) NULL,
    password_hash VARCHAR(255) NOT NULL,
    role_id BIGINT UNSIGNED NOT NULL,
    employee_id BIGINT UNSIGNED NULL,
    status ENUM('active', 'inactive', 'locked') NOT NULL DEFAULT 'active',
    last_login_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id),
    CONSTRAINT fk_users_employee FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB;

CREATE TABLE product_categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    parent_id BIGINT UNSIGNED NULL,
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(120) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_product_categories_parent FOREIGN KEY (parent_id) REFERENCES product_categories(id)
) ENGINE=InnoDB;

CREATE TABLE brands (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(120) NOT NULL,
    is_internal TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(80) NOT NULL UNIQUE,
    barcode VARCHAR(80) NULL,
    name VARCHAR(180) NOT NULL,
    category_id BIGINT UNSIGNED NOT NULL,
    brand_id BIGINT UNSIGNED NOT NULL,
    product_type ENUM('decorative_paint', 'supporting_material', 'service_material') NOT NULL,
    unit_name VARCHAR(20) NOT NULL,
    cost_price DECIMAL(15,2) NOT NULL DEFAULT 0,
    sell_price DECIMAL(15,2) NOT NULL DEFAULT 0,
    min_stock DECIMAL(14,2) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_products_category FOREIGN KEY (category_id) REFERENCES product_categories(id),
    CONSTRAINT fk_products_brand FOREIGN KEY (brand_id) REFERENCES brands(id)
) ENGINE=InnoDB;

CREATE TABLE warehouses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(120) NOT NULL,
    address TEXT NULL,
    manager_employee_id BIGINT UNSIGNED NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_warehouses_manager FOREIGN KEY (manager_employee_id) REFERENCES employees(id)
) ENGINE=InnoDB;

CREATE TABLE inventory_balances (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    warehouse_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    qty_on_hand DECIMAL(14,2) NOT NULL DEFAULT 0,
    qty_reserved DECIMAL(14,2) NOT NULL DEFAULT 0,
    min_stock_snapshot DECIMAL(14,2) NOT NULL DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_inventory_balances (warehouse_id, product_id),
    CONSTRAINT fk_inventory_balances_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
    CONSTRAINT fk_inventory_balances_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

CREATE TABLE inventory_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    trx_no VARCHAR(60) NOT NULL UNIQUE,
    trx_date DATETIME NOT NULL,
    warehouse_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    trx_type ENUM('in', 'out', 'transfer_in', 'transfer_out', 'adjust_plus', 'adjust_minus', 'reserve', 'release') NOT NULL,
    qty DECIMAL(14,2) NOT NULL,
    unit_cost DECIMAL(15,2) NULL,
    reference_type VARCHAR(40) NULL,
    reference_id BIGINT UNSIGNED NULL,
    notes VARCHAR(255) NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_inventory_transactions_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
    CONSTRAINT fk_inventory_transactions_product FOREIGN KEY (product_id) REFERENCES products(id),
    CONSTRAINT fk_inventory_transactions_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE sales_channels (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(60) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE platforms (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    channel_id BIGINT UNSIGNED NOT NULL,
    code VARCHAR(60) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    CONSTRAINT fk_platforms_channel FOREIGN KEY (channel_id) REFERENCES sales_channels(id)
) ENGINE=InnoDB;

CREATE TABLE stores (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    platform_id BIGINT UNSIGNED NOT NULL,
    store_code VARCHAR(80) NOT NULL UNIQUE,
    store_name VARCHAR(120) NOT NULL,
    store_type ENUM('official', 'regular') NOT NULL,
    owner_employee_id BIGINT UNSIGNED NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    CONSTRAINT fk_stores_platform FOREIGN KEY (platform_id) REFERENCES platforms(id),
    CONSTRAINT fk_stores_owner FOREIGN KEY (owner_employee_id) REFERENCES employees(id)
) ENGINE=InnoDB;

CREATE TABLE customers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_code VARCHAR(40) NOT NULL UNIQUE,
    name VARCHAR(150) NOT NULL,
    phone VARCHAR(30) NULL,
    email VARCHAR(120) NULL,
    address TEXT NULL,
    city VARCHAR(80) NULL,
    province VARCHAR(80) NULL,
    segment ENUM('retail', 'project', 'reseller') NOT NULL DEFAULT 'retail',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(60) NOT NULL UNIQUE,
    order_date DATETIME NOT NULL,
    customer_id BIGINT UNSIGNED NULL,
    channel_id BIGINT UNSIGNED NOT NULL,
    platform_id BIGINT UNSIGNED NULL,
    store_id BIGINT UNSIGNED NULL,
    order_source_ref VARCHAR(120) NULL,
    order_type ENUM('product', 'service', 'mixed') NOT NULL DEFAULT 'product',
    status ENUM('draft', 'confirmed', 'packed', 'shipped', 'completed', 'canceled') NOT NULL DEFAULT 'draft',
    subtotal DECIMAL(15,2) NOT NULL DEFAULT 0,
    discount_total DECIMAL(15,2) NOT NULL DEFAULT 0,
    shipping_cost DECIMAL(15,2) NOT NULL DEFAULT 0,
    tax_total DECIMAL(15,2) NOT NULL DEFAULT 0,
    grand_total DECIMAL(15,2) NOT NULL DEFAULT 0,
    created_by BIGINT UNSIGNED NULL,
    approved_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
    CONSTRAINT fk_orders_channel FOREIGN KEY (channel_id) REFERENCES sales_channels(id),
    CONSTRAINT fk_orders_platform FOREIGN KEY (platform_id) REFERENCES platforms(id),
    CONSTRAINT fk_orders_store FOREIGN KEY (store_id) REFERENCES stores(id),
    CONSTRAINT fk_orders_created_by FOREIGN KEY (created_by) REFERENCES users(id),
    CONSTRAINT fk_orders_approved_by FOREIGN KEY (approved_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE order_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    line_no INT UNSIGNED NOT NULL,
    item_type ENUM('product', 'service') NOT NULL DEFAULT 'product',
    product_id BIGINT UNSIGNED NULL,
    service_order_id BIGINT UNSIGNED NULL,
    qty DECIMAL(14,2) NOT NULL DEFAULT 1,
    unit_price DECIMAL(15,2) NOT NULL DEFAULT 0,
    discount DECIMAL(15,2) NOT NULL DEFAULT 0,
    tax DECIMAL(15,2) NOT NULL DEFAULT 0,
    line_total DECIMAL(15,2) NOT NULL DEFAULT 0,
    notes VARCHAR(255) NULL,
    CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders(id),
    CONSTRAINT fk_order_items_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

CREATE TABLE payments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payment_no VARCHAR(60) NOT NULL UNIQUE,
    order_id BIGINT UNSIGNED NOT NULL,
    payment_date DATETIME NOT NULL,
    method_name ENUM('cash', 'transfer', 'ewallet', 'cod') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    status ENUM('pending', 'paid', 'failed', 'refunded') NOT NULL DEFAULT 'pending',
    external_ref VARCHAR(120) NULL,
    received_by BIGINT UNSIGNED NULL,
    CONSTRAINT fk_payments_order FOREIGN KEY (order_id) REFERENCES orders(id),
    CONSTRAINT fk_payments_received_by FOREIGN KEY (received_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE shipments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    shipment_no VARCHAR(60) NOT NULL UNIQUE,
    order_id BIGINT UNSIGNED NOT NULL,
    warehouse_id BIGINT UNSIGNED NOT NULL,
    courier_name VARCHAR(100) NULL,
    tracking_no VARCHAR(100) NULL,
    driver_employee_id BIGINT UNSIGNED NULL,
    shipped_at DATETIME NULL,
    delivered_at DATETIME NULL,
    status ENUM('ready', 'in_transit', 'delivered', 'returned') NOT NULL DEFAULT 'ready',
    CONSTRAINT fk_shipments_order FOREIGN KEY (order_id) REFERENCES orders(id),
    CONSTRAINT fk_shipments_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
    CONSTRAINT fk_shipments_driver FOREIGN KEY (driver_employee_id) REFERENCES employees(id)
) ENGINE=InnoDB;

CREATE TABLE services (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    service_code VARCHAR(50) NOT NULL UNIQUE,
    service_name VARCHAR(150) NOT NULL,
    description TEXT NULL,
    pricing_model ENUM('per_m2', 'per_project', 'custom') NOT NULL DEFAULT 'custom',
    base_price DECIMAL(15,2) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE service_orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    service_order_no VARCHAR(60) NOT NULL UNIQUE,
    order_id BIGINT UNSIGNED NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    service_id BIGINT UNSIGNED NOT NULL,
    location_address TEXT NULL,
    area_m2 DECIMAL(12,2) NULL,
    scheduled_date DATE NULL,
    status ENUM('survey', 'quotation', 'approved', 'in_progress', 'done', 'canceled') NOT NULL DEFAULT 'survey',
    estimated_cost DECIMAL(15,2) NOT NULL DEFAULT 0,
    final_cost DECIMAL(15,2) NULL,
    uses_internal_paint TINYINT(1) NOT NULL DEFAULT 1,
    uses_external_paint TINYINT(1) NOT NULL DEFAULT 0,
    created_by BIGINT UNSIGNED NULL,
    assigned_sales_id BIGINT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_service_orders_order FOREIGN KEY (order_id) REFERENCES orders(id),
    CONSTRAINT fk_service_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
    CONSTRAINT fk_service_orders_service FOREIGN KEY (service_id) REFERENCES services(id),
    CONSTRAINT fk_service_orders_created_by FOREIGN KEY (created_by) REFERENCES users(id),
    CONSTRAINT fk_service_orders_assigned_sales FOREIGN KEY (assigned_sales_id) REFERENCES employees(id)
) ENGINE=InnoDB;

CREATE TABLE service_materials (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    service_order_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    source_type ENUM('internal_stock', 'external_purchase') NOT NULL,
    estimated_qty DECIMAL(14,2) NOT NULL DEFAULT 0,
    actual_qty DECIMAL(14,2) NULL,
    unit_cost DECIMAL(15,2) NULL,
    CONSTRAINT fk_service_materials_service_order FOREIGN KEY (service_order_id) REFERENCES service_orders(id),
    CONSTRAINT fk_service_materials_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

CREATE TABLE service_assignments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    service_order_id BIGINT UNSIGNED NOT NULL,
    employee_id BIGINT UNSIGNED NOT NULL,
    role_on_service VARCHAR(80) NOT NULL,
    assigned_at DATETIME NOT NULL,
    start_at DATETIME NULL,
    finish_at DATETIME NULL,
    status ENUM('assigned', 'on_progress', 'done', 'canceled') NOT NULL DEFAULT 'assigned',
    CONSTRAINT fk_service_assignments_service_order FOREIGN KEY (service_order_id) REFERENCES service_orders(id),
    CONSTRAINT fk_service_assignments_employee FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB;

CREATE TABLE cash_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    trx_no VARCHAR(60) NOT NULL UNIQUE,
    trx_date DATETIME NOT NULL,
    trx_direction ENUM('in', 'out') NOT NULL,
    category_name ENUM('sales', 'service_income', 'procurement', 'salary', 'operational', 'other') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    description VARCHAR(255) NULL,
    reference_type VARCHAR(40) NULL,
    reference_id BIGINT UNSIGNED NULL,
    created_by BIGINT UNSIGNED NULL,
    CONSTRAINT fk_cash_transactions_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE attendance (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NOT NULL,
    attendance_date DATE NOT NULL,
    check_in TIME NULL,
    check_out TIME NULL,
    status ENUM('present', 'late', 'leave', 'absent') NOT NULL DEFAULT 'present',
    notes VARCHAR(255) NULL,
    UNIQUE KEY uk_attendance_unique (employee_id, attendance_date),
    CONSTRAINT fk_attendance_employee FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB;

CREATE TABLE payroll_periods (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    period_code VARCHAR(20) NOT NULL UNIQUE,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status ENUM('draft', 'approved', 'paid') NOT NULL DEFAULT 'draft'
) ENGINE=InnoDB;

CREATE TABLE payrolls (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payroll_period_id BIGINT UNSIGNED NOT NULL,
    employee_id BIGINT UNSIGNED NOT NULL,
    basic_salary DECIMAL(15,2) NOT NULL DEFAULT 0,
    allowance DECIMAL(15,2) NOT NULL DEFAULT 0,
    deduction DECIMAL(15,2) NOT NULL DEFAULT 0,
    overtime DECIMAL(15,2) NOT NULL DEFAULT 0,
    net_salary DECIMAL(15,2) NOT NULL DEFAULT 0,
    status ENUM('draft', 'approved', 'paid') NOT NULL DEFAULT 'draft',
    paid_at DATETIME NULL,
    CONSTRAINT fk_payrolls_period FOREIGN KEY (payroll_period_id) REFERENCES payroll_periods(id),
    CONSTRAINT fk_payrolls_employee FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    module_name VARCHAR(80) NOT NULL,
    action_name VARCHAR(40) NOT NULL,
    entity_type VARCHAR(80) NOT NULL,
    entity_id BIGINT UNSIGNED NULL,
    old_values JSON NULL,
    new_values JSON NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_audit_logs_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE INDEX idx_orders_order_date ON orders(order_date);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_inventory_transactions_trx_date ON inventory_transactions(trx_date);
CREATE INDEX idx_service_orders_status ON service_orders(status);

INSERT INTO roles (code, name, description) VALUES
('SUPER_ADMIN', 'Super Admin', 'Akses penuh sistem'),
('GENERAL_MANAGER', 'General Manager', 'Akses eksekutif'),
('WAREHOUSE_MANAGER', 'Warehouse Manager', 'Akses gudang manager'),
('WAREHOUSE_STAFF', 'Warehouse Staff', 'Akses operasional gudang'),
('SALES_EXEC', 'Sales Executive', 'Akses penjualan'),
('SERVICE_SALES', 'Service Sales', 'Akses layanan jasa'),
('MARKETING_STAFF', 'Marketing Staff', 'Akses marketing');

INSERT INTO employees (employee_code, full_name, division, position_name, phone, join_date, employment_status, basic_salary)
VALUES
('EMP-0001', 'Administrator', 'Direksi', 'System Admin', '081234567890', CURDATE(), 'active', 15000000);

INSERT INTO users (username, email, password_hash, role_id, employee_id, status)
SELECT
    'admin',
    'admin@decopaint.local',
    '$2y$10$DceJWbmI9r0B53Ing6TNzepgunQtQFSWvyZ9Rd1DE8CrLSBuHCvSa',
    r.id,
    e.id,
    'active'
FROM roles r
JOIN employees e ON e.employee_code = 'EMP-0001'
WHERE r.code = 'SUPER_ADMIN';

INSERT INTO sales_channels (code, name) VALUES
('OFFLINE', 'Offline'),
('MARKETPLACE', 'Marketplace'),
('SOCIAL_COMMERCE', 'Social Commerce');

INSERT INTO platforms (channel_id, code, name)
SELECT id, 'WHATSAPP', 'WhatsApp' FROM sales_channels WHERE code = 'OFFLINE';

INSERT INTO platforms (channel_id, code, name)
SELECT id, 'TELEPON', 'Telepon' FROM sales_channels WHERE code = 'OFFLINE';

INSERT INTO platforms (channel_id, code, name)
SELECT id, 'SHOPEE', 'Shopee' FROM sales_channels WHERE code = 'MARKETPLACE';

INSERT INTO product_categories (code, name) VALUES
('DECORATIVE', 'Decorative Paint'),
('SUPPORTING', 'Supporting Material');

INSERT INTO brands (code, name, is_internal) VALUES
('DECO', 'DECOPAINT', 1),
('EXT01', 'Brand Lain', 0);

INSERT INTO products (sku, name, category_id, brand_id, product_type, unit_name, cost_price, sell_price, min_stock)
SELECT 'DECO-INT-001', 'Decopaint Interior Premium 5L', c.id, b.id, 'decorative_paint', 'liter', 120000, 175000, 20
FROM product_categories c, brands b
WHERE c.code = 'DECORATIVE' AND b.code = 'DECO';

INSERT INTO warehouses (code, name, address, is_active)
VALUES ('WH-UTAMA', 'Gudang Utama', 'Jl. Warehouse No. 1', 1);

INSERT INTO inventory_balances (warehouse_id, product_id, qty_on_hand, qty_reserved, min_stock_snapshot)
SELECT w.id, p.id, 100, 0, p.min_stock
FROM warehouses w, products p
WHERE w.code = 'WH-UTAMA' AND p.sku = 'DECO-INT-001';

INSERT INTO customers (customer_code, name, phone, segment)
VALUES ('CUST-0001', 'Pelanggan Umum', '081111111111', 'retail');

INSERT INTO services (service_code, service_name, description, pricing_model, base_price, is_active)
VALUES ('SRV-CAT-001', 'Jasa Pengecatan Interior', 'Layanan pengecatan interior rumah', 'per_m2', 45000, 1);