SET NAMES utf8mb4;
SET time_zone = '+03:00';

CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(160) NOT NULL,
    phone VARCHAR(32) NULL,
    email VARCHAR(190) NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('manager', 'employee') NOT NULL,
    status ENUM('active', 'suspended') NOT NULL DEFAULT 'active',
    must_change_password TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at DATETIME NULL,
    created_at DATETIME NULL,
    updated_at DATETIME NULL,
    UNIQUE KEY users_phone_unique (phone),
    UNIQUE KEY users_email_unique (email),
    KEY users_role_status_index (role, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS api_tokens (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    token_hash CHAR(64) NOT NULL,
    device_name VARCHAR(120) NULL,
    last_used_at DATETIME NULL,
    expires_at DATETIME NOT NULL,
    revoked_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    UNIQUE KEY api_tokens_hash_unique (token_hash),
    KEY api_tokens_user_index (user_id, revoked_at),
    CONSTRAINT api_tokens_user_fk FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS login_attempts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    identifier VARCHAR(190) NOT NULL,
    ip_address VARCHAR(64) NOT NULL,
    succeeded TINYINT(1) NOT NULL DEFAULT 0,
    attempted_at DATETIME NOT NULL,
    KEY login_attempts_identifier_index (identifier, attempted_at),
    KEY login_attempts_ip_index (ip_address, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS deals (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(190) NOT NULL,
    source VARCHAR(190) NULL,
    purchase_cost DECIMAL(14,2) NULL,
    transport_cost DECIMAL(14,2) NULL,
    status ENUM('draft', 'active', 'closed') NOT NULL DEFAULT 'draft',
    created_by BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY deals_status_index (status),
    CONSTRAINT deals_created_by_fk FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS deal_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    deal_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(190) NOT NULL,
    expected_quantity DECIMAL(12,3) NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY deal_items_deal_index (deal_id),
    CONSTRAINT deal_items_deal_fk FOREIGN KEY (deal_id) REFERENCES deals(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS batches (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    deal_item_id BIGINT UNSIGNED NOT NULL,
    expected_quantity DECIMAL(12,3) NULL,
    purchase_cost DECIMAL(14,2) NULL,
    transport_cost DECIMAL(14,2) NULL,
    notes TEXT NULL,
    status ENUM('pending', 'received', 'inventory_review', 'approved') NOT NULL DEFAULT 'pending',
    created_by BIGINT UNSIGNED NOT NULL,
    received_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY batches_item_status_index (deal_item_id, status),
    CONSTRAINT batches_item_fk FOREIGN KEY (deal_item_id) REFERENCES deal_items(id),
    CONSTRAINT batches_created_by_fk FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS assignments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    deal_id BIGINT UNSIGNED NOT NULL,
    deal_item_id BIGINT UNSIGNED NULL,
    batch_id BIGINT UNSIGNED NULL,
    employee_id BIGINT UNSIGNED NOT NULL,
    is_shared TINYINT(1) NOT NULL DEFAULT 0,
    status ENUM('active', 'revoked') NOT NULL DEFAULT 'active',
    assigned_by BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY assignments_employee_index (employee_id, status),
    KEY assignments_target_index (deal_id, deal_item_id, batch_id),
    CONSTRAINT assignments_deal_fk FOREIGN KEY (deal_id) REFERENCES deals(id),
    CONSTRAINT assignments_item_fk FOREIGN KEY (deal_item_id) REFERENCES deal_items(id),
    CONSTRAINT assignments_batch_fk FOREIGN KEY (batch_id) REFERENCES batches(id),
    CONSTRAINT assignments_employee_fk FOREIGN KEY (employee_id) REFERENCES users(id),
    CONSTRAINT assignments_manager_fk FOREIGN KEY (assigned_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS batch_receipts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    batch_id BIGINT UNSIGNED NOT NULL,
    employee_id BIGINT UNSIGNED NOT NULL,
    decision ENUM('accepted', 'rejected') NOT NULL,
    notes TEXT NULL,
    decided_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY batch_receipts_employee_unique (batch_id, employee_id),
    CONSTRAINT batch_receipts_batch_fk FOREIGN KEY (batch_id) REFERENCES batches(id),
    CONSTRAINT batch_receipts_employee_fk FOREIGN KEY (employee_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inventories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    batch_id BIGINT UNSIGNED NOT NULL,
    status ENUM('draft', 'submitted', 'approved', 'changes_requested') NOT NULL DEFAULT 'draft',
    change_reason TEXT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    updated_by BIGINT UNSIGNED NOT NULL,
    submitted_at DATETIME NULL,
    approved_by BIGINT UNSIGNED NULL,
    approved_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY inventories_batch_unique (batch_id),
    CONSTRAINT inventories_batch_fk FOREIGN KEY (batch_id) REFERENCES batches(id),
    CONSTRAINT inventories_created_by_fk FOREIGN KEY (created_by) REFERENCES users(id),
    CONSTRAINT inventories_updated_by_fk FOREIGN KEY (updated_by) REFERENCES users(id),
    CONSTRAINT inventories_approved_by_fk FOREIGN KEY (approved_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inventory_lines (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    inventory_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(220) NOT NULL,
    quantity DECIMAL(12,3) NOT NULL,
    unit VARCHAR(40) NOT NULL DEFAULT 'piece',
    condition_label VARCHAR(80) NULL,
    serial_number VARCHAR(190) NULL,
    suggested_price DECIMAL(14,2) NULL,
    unit_cost DECIMAL(14,2) NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY inventory_lines_inventory_index (inventory_id),
    CONSTRAINT inventory_lines_inventory_fk FOREIGN KEY (inventory_id) REFERENCES inventories(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    batch_id BIGINT UNSIGNED NOT NULL,
    inventory_line_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(220) NOT NULL,
    unit VARCHAR(40) NOT NULL DEFAULT 'piece',
    condition_label VARCHAR(80) NULL,
    serial_number VARCHAR(190) NULL,
    suggested_price DECIMAL(14,2) NULL,
    unit_cost DECIMAL(14,2) NULL,
    initial_quantity DECIMAL(12,3) NOT NULL,
    available_quantity DECIMAL(12,3) NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY products_inventory_line_unique (inventory_line_id),
    UNIQUE KEY products_serial_unique (serial_number),
    KEY products_batch_name_index (batch_id, name),
    CONSTRAINT products_batch_fk FOREIGN KEY (batch_id) REFERENCES batches(id),
    CONSTRAINT products_inventory_line_fk FOREIGN KEY (inventory_line_id) REFERENCES inventory_lines(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS stock_movements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    employee_id BIGINT UNSIGNED NULL,
    quantity_delta DECIMAL(12,3) NOT NULL,
    movement_type ENUM('inventory', 'sale', 'sale_reversal', 'return', 'damage', 'shortage', 'transfer_in', 'transfer_out', 'adjustment') NOT NULL,
    reference_type VARCHAR(80) NOT NULL,
    reference_id BIGINT UNSIGNED NOT NULL,
    occurred_at DATETIME NOT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL,
    KEY stock_movements_product_index (product_id, occurred_at),
    KEY stock_movements_reference_index (reference_type, reference_id),
    CONSTRAINT stock_movements_product_fk FOREIGN KEY (product_id) REFERENCES products(id),
    CONSTRAINT stock_movements_employee_fk FOREIGN KEY (employee_id) REFERENCES users(id),
    CONSTRAINT stock_movements_created_by_fk FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    owner_employee_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(190) NOT NULL,
    phone VARCHAR(32) NULL,
    address TEXT NULL,
    notes TEXT NULL,
    status ENUM('active', 'archived') NOT NULL DEFAULT 'active',
    created_by BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY customers_owner_phone_unique (owner_employee_id, phone),
    KEY customers_owner_name_index (owner_employee_id, name),
    CONSTRAINT customers_owner_fk FOREIGN KEY (owner_employee_id) REFERENCES users(id),
    CONSTRAINT customers_created_by_fk FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sales (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sale_number VARCHAR(40) NOT NULL,
    employee_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NULL,
    payment_type ENUM('cash', 'credit', 'mixed') NOT NULL,
    payment_method ENUM('cash', 'instapay', 'wallet', 'bank_transfer', 'other') NULL,
    total DECIMAL(14,2) NOT NULL,
    paid_now DECIMAL(14,2) NOT NULL DEFAULT 0,
    due_at DATE NULL,
    notes TEXT NULL,
    status ENUM('confirmed', 'reversed') NOT NULL DEFAULT 'confirmed',
    sold_at DATETIME NOT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    reversal_reason TEXT NULL,
    reversed_by BIGINT UNSIGNED NULL,
    reversed_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY sales_number_unique (sale_number),
    KEY sales_employee_date_index (employee_id, sold_at),
    KEY sales_customer_index (customer_id, status),
    CONSTRAINT sales_employee_fk FOREIGN KEY (employee_id) REFERENCES users(id),
    CONSTRAINT sales_customer_fk FOREIGN KEY (customer_id) REFERENCES customers(id),
    CONSTRAINT sales_created_by_fk FOREIGN KEY (created_by) REFERENCES users(id),
    CONSTRAINT sales_reversed_by_fk FOREIGN KEY (reversed_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sale_lines (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sale_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    quantity DECIMAL(12,3) NOT NULL,
    unit_price DECIMAL(14,2) NOT NULL,
    unit_cost_snapshot DECIMAL(14,2) NULL,
    line_total DECIMAL(14,2) NOT NULL,
    created_at DATETIME NOT NULL,
    KEY sale_lines_sale_index (sale_id),
    CONSTRAINT sale_lines_sale_fk FOREIGN KEY (sale_id) REFERENCES sales(id),
    CONSTRAINT sale_lines_product_fk FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customer_ledger (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT UNSIGNED NOT NULL,
    amount_delta DECIMAL(14,2) NOT NULL,
    entry_type ENUM('sale', 'collection', 'sale_reversal', 'return', 'adjustment') NOT NULL,
    reference_type VARCHAR(80) NOT NULL,
    reference_id BIGINT UNSIGNED NOT NULL,
    description VARCHAR(255) NULL,
    occurred_at DATETIME NOT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL,
    KEY customer_ledger_customer_date_index (customer_id, occurred_at),
    KEY customer_ledger_reference_index (reference_type, reference_id),
    CONSTRAINT customer_ledger_customer_fk FOREIGN KEY (customer_id) REFERENCES customers(id),
    CONSTRAINT customer_ledger_created_by_fk FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS collections (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT UNSIGNED NOT NULL,
    responsible_employee_id BIGINT UNSIGNED NOT NULL,
    recorded_by BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(14,2) NOT NULL,
    payment_method ENUM('cash', 'instapay', 'wallet', 'bank_transfer', 'other') NOT NULL,
    notes TEXT NULL,
    status ENUM('confirmed', 'reversed') NOT NULL DEFAULT 'confirmed',
    collected_at DATETIME NOT NULL,
    reversal_reason TEXT NULL,
    reversed_by BIGINT UNSIGNED NULL,
    reversed_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY collections_customer_date_index (customer_id, collected_at),
    CONSTRAINT collections_customer_fk FOREIGN KEY (customer_id) REFERENCES customers(id),
    CONSTRAINT collections_employee_fk FOREIGN KEY (responsible_employee_id) REFERENCES users(id),
    CONSTRAINT collections_recorded_by_fk FOREIGN KEY (recorded_by) REFERENCES users(id),
    CONSTRAINT collections_reversed_by_fk FOREIGN KEY (reversed_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cash_movements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_type ENUM('employee', 'company') NOT NULL,
    account_user_id BIGINT UNSIGNED NULL,
    amount_delta DECIMAL(14,2) NOT NULL,
    movement_type ENUM('sale_payment', 'collection', 'remittance', 'sale_reversal', 'expense', 'unallocated', 'unallocated_settlement', 'adjustment') NOT NULL,
    payment_method ENUM('cash', 'instapay', 'wallet', 'bank_transfer', 'other') NOT NULL,
    reference_type VARCHAR(80) NOT NULL,
    reference_id BIGINT UNSIGNED NOT NULL,
    occurred_at DATETIME NOT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL,
    KEY cash_movements_account_index (account_type, account_user_id, occurred_at),
    KEY cash_movements_reference_index (reference_type, reference_id),
    CONSTRAINT cash_movements_account_user_fk FOREIGN KEY (account_user_id) REFERENCES users(id),
    CONSTRAINT cash_movements_created_by_fk FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS remittances (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NOT NULL,
    requested_amount DECIMAL(14,2) NOT NULL,
    received_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
    payment_method ENUM('cash', 'instapay', 'wallet', 'bank_transfer', 'other') NOT NULL,
    status ENUM('pending', 'partially_received', 'received', 'rejected') NOT NULL DEFAULT 'pending',
    requested_at DATETIME NOT NULL,
    requested_by BIGINT UNSIGNED NOT NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY remittances_employee_status_index (employee_id, status),
    CONSTRAINT remittances_employee_fk FOREIGN KEY (employee_id) REFERENCES users(id),
    CONSTRAINT remittances_requested_by_fk FOREIGN KEY (requested_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS remittance_receipts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    remittance_id BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(14,2) NOT NULL,
    received_by BIGINT UNSIGNED NOT NULL,
    notes TEXT NULL,
    received_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL,
    KEY remittance_receipts_remittance_index (remittance_id),
    CONSTRAINT remittance_receipts_remittance_fk FOREIGN KEY (remittance_id) REFERENCES remittances(id),
    CONSTRAINT remittance_receipts_manager_fk FOREIGN KEY (received_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS unallocated_amounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(14,2) NOT NULL,
    settled_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
    payment_method ENUM('cash', 'instapay', 'wallet', 'bank_transfer', 'other') NOT NULL,
    notes TEXT NOT NULL,
    status ENUM('pending', 'partially_settled', 'settled') NOT NULL DEFAULT 'pending',
    reported_by BIGINT UNSIGNED NOT NULL,
    reported_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY unallocated_employee_status_index (employee_id, status),
    CONSTRAINT unallocated_employee_fk FOREIGN KEY (employee_id) REFERENCES users(id),
    CONSTRAINT unallocated_reported_by_fk FOREIGN KEY (reported_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS unallocated_settlements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    unallocated_amount_id BIGINT UNSIGNED NOT NULL,
    sale_id BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(14,2) NOT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL,
    KEY unallocated_settlements_amount_index (unallocated_amount_id),
    KEY unallocated_settlements_sale_index (sale_id),
    CONSTRAINT unallocated_settlements_amount_fk FOREIGN KEY (unallocated_amount_id) REFERENCES unallocated_amounts(id),
    CONSTRAINT unallocated_settlements_sale_fk FOREIGN KEY (sale_id) REFERENCES sales(id),
    CONSTRAINT unallocated_settlements_created_by_fk FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    type VARCHAR(80) NOT NULL,
    title VARCHAR(190) NOT NULL,
    body VARCHAR(500) NOT NULL,
    entity_type VARCHAR(80) NULL,
    entity_id BIGINT UNSIGNED NULL,
    read_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    KEY notifications_user_read_index (user_id, read_at, created_at),
    CONSTRAINT notifications_user_fk FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    action VARCHAR(100) NOT NULL,
    auditable_type VARCHAR(80) NOT NULL,
    auditable_id BIGINT UNSIGNED NOT NULL,
    before_json JSON NULL,
    after_json JSON NULL,
    reason TEXT NULL,
    ip_address VARCHAR(64) NULL,
    created_at DATETIME NOT NULL,
    KEY audit_logs_target_index (auditable_type, auditable_id),
    KEY audit_logs_user_date_index (user_id, created_at),
    CONSTRAINT audit_logs_user_fk FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;