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


CREATE TABLE owners (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE branches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    owner_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_branch_owner FOREIGN KEY (owner_id) REFERENCES owners(id)
);

CREATE TABLE accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    owner_id INT NOT NULL,
    branch_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    account_number VARCHAR(50) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_account_owner FOREIGN KEY (owner_id) REFERENCES owners(id),
    CONSTRAINT fk_account_branch FOREIGN KEY (branch_id) REFERENCES branches(id)
);

CREATE TABLE user_groups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    group_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(120) NULL UNIQUE,
    phone VARCHAR(30) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    remember_token VARCHAR(64) NULL,
    remember_expired_at DATETIME NULL,
    is_active TINYINT(1) DEFAULT 1,
    is_verified TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_group FOREIGN KEY (group_id) REFERENCES user_groups(id)
);

CREATE TABLE modules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(60) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE module_actions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    module_id INT NOT NULL,
    action_code VARCHAR(30) NOT NULL,
    action_name VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_action_module FOREIGN KEY (module_id) REFERENCES modules(id),
    UNIQUE KEY uniq_module_action (module_id, action_code)
);

CREATE TABLE group_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    group_id INT NOT NULL,
    module_action_id INT NOT NULL,
    is_allowed TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_permission_group FOREIGN KEY (group_id) REFERENCES user_groups(id),
    CONSTRAINT fk_permission_action FOREIGN KEY (module_action_id) REFERENCES module_actions(id),
    UNIQUE KEY uniq_permission (group_id, module_action_id)
);

CREATE TABLE transaction_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    owner_id INT NOT NULL,
    code VARCHAR(30) NOT NULL,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_tx_type_owner FOREIGN KEY (owner_id) REFERENCES owners(id)
);

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    owner_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    flow ENUM('IN','OUT') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_category_owner FOREIGN KEY (owner_id) REFERENCES owners(id)
);

CREATE TABLE transactions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    owner_id INT NOT NULL,
    branch_id INT NOT NULL,
    account_id INT NOT NULL,
    transaction_type_id INT NOT NULL,
    category_id INT NOT NULL,
    flow ENUM('IN','OUT') NOT NULL,
    amount DECIMAL(18,2) NOT NULL,
    note VARCHAR(255) DEFAULT NULL,
    transaction_date DATE NOT NULL,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_tx_owner FOREIGN KEY (owner_id) REFERENCES owners(id),
    CONSTRAINT fk_tx_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
    CONSTRAINT fk_tx_account FOREIGN KEY (account_id) REFERENCES accounts(id),
    CONSTRAINT fk_tx_type FOREIGN KEY (transaction_type_id) REFERENCES transaction_types(id),
    CONSTRAINT fk_tx_category FOREIGN KEY (category_id) REFERENCES categories(id),
    CONSTRAINT fk_tx_user FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE audit_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    action VARCHAR(50) NOT NULL,
    module VARCHAR(80) NOT NULL,
    detail TEXT NULL,
    ip_address VARCHAR(50) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE auth_otps (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    purpose ENUM('REGISTER_ACTIVATION','FORGOT_PASSWORD') NOT NULL,
    code VARCHAR(255) NOT NULL,
    expired_at DATETIME NOT NULL,
    used_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_auth_otp_user FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT INTO owners (id, name) VALUES (1, 'Owner Utama');
INSERT INTO branches (id, owner_id, name) VALUES (1, 1, 'Cabang Pusat');
INSERT INTO accounts (id, owner_id, branch_id, name, account_number) VALUES (1, 1, 1, 'Kas Utama', 'KAS-001');

INSERT INTO user_groups (id, name) VALUES (1, 'Super Admin'), (2, 'Operator');

INSERT INTO users (group_id, name, email, phone, password_hash, is_active, is_verified) VALUES
(1, 'Administrator', 'admin@kas.local', '628123456789', '$2y$10$s5HGZsTgcoezSzqSM/S9SezkDoaIQZQ4Pc72Zxx/IiM8qyIGjs5F2', 1, 1);

INSERT INTO modules (id, code, name) VALUES
(1, 'DASHBOARD', 'Dashboard'),
(2, 'USERS', 'Users'),
(3, 'RBAC', 'RBAC'),
(4, 'MASTER', 'Master Data'),
(5, 'OWNER_BRANCH', 'Owner Branch'),
(6, 'EXECUTIVE', 'Executive Dashboard'),
(7, 'AUDIT_LOG', 'Audit Log');

INSERT INTO module_actions (module_id, action_code, action_name) VALUES
(1, 'VIEW', 'View'), (1, 'CREATE', 'Create'), (1, 'UPDATE', 'Update'), (1, 'DELETE', 'Delete'),
(2, 'VIEW', 'View'), (2, 'CREATE', 'Create'), (2, 'UPDATE', 'Update'), (2, 'DELETE', 'Delete'),
(3, 'VIEW', 'View'), (3, 'CREATE', 'Create'), (3, 'UPDATE', 'Update'), (3, 'DELETE', 'Delete'),
(4, 'VIEW', 'View'), (4, 'CREATE', 'Create'), (4, 'UPDATE', 'Update'), (4, 'DELETE', 'Delete'),
(5, 'VIEW', 'View'), (5, 'CREATE', 'Create'), (5, 'UPDATE', 'Update'), (5, 'DELETE', 'Delete'),
(6, 'VIEW', 'View'), (6, 'CREATE', 'Create'), (6, 'UPDATE', 'Update'), (6, 'DELETE', 'Delete'),
(7, 'VIEW', 'View'), (7, 'CREATE', 'Create'), (7, 'UPDATE', 'Update'), (7, 'DELETE', 'Delete');

INSERT INTO group_permissions (group_id, module_action_id, is_allowed)
SELECT 1, id, 1 FROM module_actions;

INSERT INTO module_actions (module_id, action_code, action_name)
SELECT m.id, 'VIEW', 'View' FROM modules m
WHERE NOT EXISTS (
    SELECT 1 FROM module_actions ma WHERE ma.module_id = m.id AND ma.action_code = 'VIEW'
);

INSERT INTO group_permissions (group_id, module_action_id, is_allowed)
SELECT 2, ma.id, CASE WHEN ma.action_code = 'VIEW' THEN 1 ELSE 0 END
FROM module_actions ma
WHERE NOT EXISTS (
    SELECT 1 FROM group_permissions gp WHERE gp.group_id = 2 AND gp.module_action_id = ma.id
);

INSERT INTO transaction_types (owner_id, code, name) VALUES
(1, 'MUSH', 'Kas Mushollah'),
(1, 'INFAQ', 'Infaq'),
(1, 'PKK', 'PKK'),
(1, 'RT', 'RT');

INSERT INTO categories (owner_id, name, flow) VALUES
(1, 'Iuran Warga', 'IN'),
(1, 'Donasi', 'IN'),
(1, 'Operasional', 'OUT'),
(1, 'Sosial', 'OUT');

INSERT INTO transactions (owner_id, branch_id, account_id, transaction_type_id, category_id, flow, amount, note, transaction_date, created_by) VALUES
(1, 1, 1, 1, 1, 'IN', 1500000, 'Iuran bulanan', CURDATE(), 1),
(1, 1, 1, 2, 2, 'IN', 500000, 'Donasi Jumat', DATE_SUB(CURDATE(), INTERVAL 1 DAY), 1),
(1, 1, 1, 3, 3, 'OUT', 350000, 'Biaya kebersihan', DATE_SUB(CURDATE(), INTERVAL 2 DAY), 1),
(1, 1, 1, 4, 4, 'OUT', 250000, 'Kegiatan sosial', DATE_SUB(CURDATE(), INTERVAL 3 DAY), 1);
