CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(120) NOT NULL,
    username VARCHAR(80) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(30) NOT NULL,
    unit VARCHAR(120),
    is_active_user BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE accreditation_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    criterion VARCHAR(120) NOT NULL,
    indicator VARCHAR(255) NOT NULL,
    description TEXT,
    target INT DEFAULT 100,
    progress INT DEFAULT 0,
    status VARCHAR(30) DEFAULT 'Draft',
    pic VARCHAR(120),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE quality_standards (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    owner_unit VARCHAR(120) NOT NULL,
    target_value DECIMAL(10,2) DEFAULT 0,
    actual_value DECIMAL(10,2) DEFAULT 0,
    cycle_status VARCHAR(30) DEFAULT 'Penetapan',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE audit_findings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    standard_code VARCHAR(20),
    unit VARCHAR(120) NOT NULL,
    severity VARCHAR(20) DEFAULT 'Medium',
    status VARCHAR(30) DEFAULT 'Open',
    due_date DATE,
    follow_up TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_audit_standard_code FOREIGN KEY (standard_code) REFERENCES quality_standards(code)
);

CREATE TABLE finance_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    trans_date DATE NOT NULL,
    category VARCHAR(30) NOT NULL,
    source_type VARCHAR(50) NOT NULL,
    amount DECIMAL(18,2) NOT NULL,
    description TEXT,
    unit VARCHAR(120),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    module VARCHAR(50) NOT NULL,
    category VARCHAR(80),
    filename VARCHAR(255) NOT NULL,
    filepath VARCHAR(255) NOT NULL,
    version VARCHAR(20) DEFAULT '1.0',
    status VARCHAR(30) DEFAULT 'Draft',
    uploaded_by VARCHAR(120) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
