-- PRISMA SaaS Database Schema
-- Multi-tenant Corporate Work Plans and Budgets (RKAP) tracking system

-- Stores company entities (e.g., InJourney, API, ITDC)
CREATE TABLE companies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- The core table for all RKAP data, with all required fields
CREATE TABLE rkap_programs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    company_id INT NOT NULL,

    -- Program Details
    category VARCHAR(255),
    sub_category VARCHAR(255),
    program_name TEXT NOT NULL,
    cost_center VARCHAR(255),  -- NEW FIELD
    beban VARCHAR(255),        -- NEW FIELD

    -- Financials & Status
    type ENUM('CAPEX', 'OPEX') NOT NULL,
    proposed_amount DECIMAL(15, 2) NOT NULL,
    approved_amount DECIMAL(15, 2),
    realized_amount DECIMAL(15, 2),
    status ENUM('Pengajuan', 'Disetujui', 'Selesai', 'Ditolak') NOT NULL DEFAULT 'Pengajuan',
    
    -- Metadata
    notes TEXT, -- For admin comments on approval/rejection
    year INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    -- Foreign Keys for relational integrity
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (company_id) REFERENCES companies(id),

    -- Indexes for performance on common queries
    INDEX idx_year_status (year, status),
    INDEX idx_company_id (company_id)
);

-- Tracks change requests from 'users' to 'admins' for the approval workflow
CREATE TABLE approval_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    rkap_program_id INT NOT NULL,
    requested_by_user_id INT NOT NULL,
    field_changed VARCHAR(255) NOT NULL,
    old_value TEXT,
    new_value TEXT NOT NULL,
    status ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
    admin_notes TEXT, -- Admin's reason for approval/rejection
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (rkap_program_id) REFERENCES rkap_programs(id) ON DELETE CASCADE,
    FOREIGN KEY (requested_by_user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Provides a complete audit trail of all significant actions in the system
CREATE TABLE activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    company_id INT, -- Denormalized for easier filtering by Admins
    action_type VARCHAR(255) NOT NULL, -- e.g., 'CREATE_PROGRAM', 'APPROVE_CHANGE'
    details JSON, -- Store contextual data, e.g., {'program_id': 123, 'change': 'status from X to Y'}
    ip_address VARCHAR(45), -- IPv4/IPv6 address
    user_agent TEXT, -- Browser/client information
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- API logs for request/response tracking
CREATE TABLE api_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    endpoint VARCHAR(255) NOT NULL,
    method VARCHAR(10) NOT NULL,
    status_code INT NOT NULL,
    response_time_ms INT,
    request_data JSON,
    response_data JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Insert sample data for development
INSERT INTO companies (name) VALUES 
('InJourney'),
('API'),
('ITDC'),
('Sample Company');

-- Insert sample RKAP programs
INSERT INTO rkap_programs (user_id, company_id, category, sub_category, program_name, cost_center, beban, type, proposed_amount, approved_amount, realized_amount, status, year) VALUES 
(3, 1, 'Technology', 'Software Development', 'ERP System Upgrade', 'IT-001', 'Software License', 'CAPEX', 500000000, 450000000, 300000000, 'Disetujui', 2024),
(3, 1, 'Operations', 'Facility Management', 'Office Renovation', 'FAC-001', 'Construction', 'CAPEX', 200000000, 180000000, 120000000, 'Disetujui', 2024),
(3, 1, 'Human Resources', 'Training', 'Employee Development Program', 'HR-001', 'Training Cost', 'OPEX', 50000000, 45000000, 30000000, 'Disetujui', 2024),
(3, 2, 'Marketing', 'Digital Marketing', 'Social Media Campaign', 'MKT-001', 'Advertising', 'OPEX', 100000000, 90000000, 75000000, 'Disetujui', 2024),
(3, 2, 'Technology', 'Infrastructure', 'Server Upgrade', 'IT-002', 'Hardware', 'CAPEX', 300000000, 280000000, 200000000, 'Disetujui', 2024);
