CREATE TABLE sys_roles (
    role_code VARCHAR(30) PRIMARY KEY,
    role_name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE sys_menus (
    menu_code VARCHAR(50) PRIMARY KEY,
    label VARCHAR(100) NOT NULL,
    path VARCHAR(200) NOT NULL,
    icon VARCHAR(50),
    section VARCHAR(50),
    order_num INT DEFAULT 0,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE sys_role_menus (
    role_code VARCHAR(30) REFERENCES sys_roles(role_code) ON DELETE CASCADE,
    menu_code VARCHAR(50) REFERENCES sys_menus(menu_code) ON DELETE CASCADE,
    PRIMARY KEY (role_code, menu_code)
);

ALTER TABLE users DROP CONSTRAINT IF EXISTS users_role_check;
ALTER TABLE users ADD COLUMN IF NOT EXISTS permissions JSONB DEFAULT '[]'::jsonb;

-- Seed Data Roles
INSERT INTO sys_roles (role_code, role_name, description) VALUES
('super_admin', 'Super Administrator', 'Akses Penuh'),
('admin', 'Administrator', 'Akses Operasional & Laporan'),
('manager', 'Manager', 'Akses Laporan & Approval'),
('cashier', 'Kasir', 'Akses Transaksi Harian'),
('member', 'Anggota Koperasi', 'Akses Portal Anggota')
ON CONFLICT (role_code) DO NOTHING;

-- Seed Data Menus
INSERT INTO sys_menus (menu_code, label, path, icon, section, order_num) VALUES
('DASHBOARD', 'Dashboard', '/admin/dashboard', 'LayoutDashboard', 'Utama', 1),
('MEMBERS', 'Data Anggota', '/admin/members', 'Users', 'Keanggotaan', 2),

('SAVINGS_TELLER', 'Teller Simpanan', '/admin/savings/teller', 'PiggyBank', 'Simpanan', 3),
('SAVINGS_TRANSACTIONS', 'Riwayat Simpanan', '/admin/savings/transactions', 'FileText', 'Simpanan', 4),
('SAVINGS_PRODUCTS', 'Produk Simpanan', '/admin/savings-products', 'Settings', 'Simpanan', 5),

('LOAN_APPLICATIONS', 'Pengajuan Pinjaman', '/admin/loans/applications', 'FileText', 'Pinjaman', 6),
('LOAN_PAYMENTS', 'Pembayaran Pinjaman', '/admin/loans/payments', 'CreditCard', 'Pinjaman', 7),
('LOAN_COLLECTIBILITY', 'Kolektibilitas', '/admin/loans/collectibility', 'Shield', 'Pinjaman', 8),
('LOAN_PRODUCTS', 'Produk Pinjaman', '/admin/loan-products', 'Settings', 'Pinjaman', 9),

('ACC_JOURNAL', 'Jurnal Umum', '/admin/accounting/journal', 'BookOpen', 'Akuntansi', 10),
('ACC_REPORTS', 'Laporan Keuangan', '/admin/accounting/reports', 'BarChart3', 'Akuntansi', 11),
('ACC_COA', 'Chart of Accounts', '/admin/coa', 'FileText', 'Akuntansi', 12),
('ACC_SHU', 'Kalkulator SHU', '/admin/accounting/shu', 'Calculator', 'Akuntansi', 13),

('PPOB_KASIR', 'Kasir PPOB', '/admin/ppob/kasir', 'Zap', 'Layanan', 14),
('PPOB_TRANSACTIONS', 'Riwayat PPOB', '/admin/ppob/transactions', 'FileText', 'Layanan', 15),
('PPOB_MANAGEMENT', 'Produk PPOB', '/admin/ppob/management', 'Settings', 'Layanan', 16),

('WASSERBA_POS', 'Kasir WasSerba', '/admin/shop/pos', 'ShoppingBag', 'Layanan', 17),
('WASSERBA_PRODUCTS', 'Gudang WasSerba', '/admin/shop/products', 'Package', 'Layanan', 18),
('WASSERBA_OPNAME', 'Stock Opname', '/admin/shop/opname', 'FileText', 'Layanan', 19),

('COMMUNITY', 'Komunitas', '/admin/community', 'Heart', 'Layanan', 20),

('SYS_PROFILE', 'Profil Koperasi', '/admin/settings', 'UserCheck', 'Sistem', 21),
('SYS_SETTINGS', 'Pengaturan Sistem', '/admin/settings/system', 'Zap', 'Sistem', 22),
('SYS_USERS', 'Manajemen Pengguna', '/admin/settings/users', 'Users', 'Sistem', 23),
('SYS_ROLES', 'Manajemen Role', '/admin/settings/roles', 'Shield', 'Sistem', 24)
ON CONFLICT (menu_code) DO NOTHING;

-- Assign ALL Menus to Super Admin
INSERT INTO sys_role_menus (role_code, menu_code)
SELECT 'super_admin', menu_code FROM sys_menus
ON CONFLICT DO NOTHING;

-- Assign specific menus to Admin
INSERT INTO sys_role_menus (role_code, menu_code)
SELECT 'admin', menu_code FROM sys_menus WHERE menu_code NOT IN ('SYS_USERS', 'SYS_ROLES')
ON CONFLICT DO NOTHING;

-- Assign specific menus to Cashier
INSERT INTO sys_role_menus (role_code, menu_code)
SELECT 'cashier', menu_code FROM sys_menus WHERE menu_code IN (
  'DASHBOARD', 'SAVINGS_TELLER', 'PPOB_KASIR', 'WASSERBA_POS', 'WASSERBA_OPNAME'
)
ON CONFLICT DO NOTHING;

-- Assign specific menus to Manager
INSERT INTO sys_role_menus (role_code, menu_code)
SELECT 'manager', menu_code FROM sys_menus WHERE section != 'Sistem'
ON CONFLICT DO NOTHING;