-- Phase 11: Checks module phase 1

CREATE TABLE IF NOT EXISTS checks (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    check_no VARCHAR(100) NOT NULL,
    amount DECIMAL(18,2) NOT NULL,
    drawer_party_id BIGINT UNSIGNED NOT NULL,
    bank_name VARCHAR(150) NULL,
    issue_date DATE NULL,
    due_date DATE NULL,
    receive_date DATETIME NOT NULL,
    current_status ENUM('received','endorsed','returned','canceled') NOT NULL DEFAULT 'received',
    current_holder_type ENUM('company','supplier','customer') NOT NULL DEFAULT 'company',
    current_holder_party_id BIGINT UNSIGNED NULL,
    last_endorsed_party_id BIGINT UNSIGNED NULL,
    notes TEXT NULL,
    user_id BIGINT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_checks_status_due (current_status, due_date),
    KEY idx_checks_drawer (drawer_party_id),
    CONSTRAINT fk_checks_drawer_party FOREIGN KEY (drawer_party_id) REFERENCES parties(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS check_movements (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    check_id BIGINT NOT NULL,
    movement_type ENUM('receive','endorse','return') NOT NULL,
    movement_date DATETIME NOT NULL,
    party_id BIGINT UNSIGNED NULL,
    amount DECIMAL(18,2) NOT NULL,
    notes TEXT NULL,
    user_id BIGINT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_check_movements_check_date (check_id, movement_date),
    CONSTRAINT fk_check_movements_check FOREIGN KEY (check_id) REFERENCES checks(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (code, name, module) VALUES
('checks.view', 'عرض الشيكات', 'checks'),
('checks.create', 'استلام شيك من عميل', 'checks'),
('checks.endorse', 'تظهير شيك لمورد', 'checks'),
('checks.return', 'رجوع شيك', 'checks');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 1, p.id FROM permissions p
WHERE p.code IN ('checks.view','checks.create','checks.endorse','checks.return');