-- Database Schema untuk Aplikasi TTD SMAN 1 Tanjungbalai
-- Created by T. Rifaldo Sany

CREATE DATABASE IF NOT EXISTS ttdsmansa;
USE ttdsmansa;

-- Tabel Documents untuk menyimpan data surat
CREATE TABLE documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    document_number VARCHAR(100) UNIQUE NOT NULL,
    title VARCHAR(255) NOT NULL,
    subject TEXT NOT NULL,
    description TEXT,
    file_path VARCHAR(255) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_size INT,
    created_by INT NOT NULL,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    approved_by INT NULL,
    approved_at TIMESTAMP NULL,
    rejection_reason TEXT NULL,
    qr_code_path VARCHAR(255) NULL,
    verification_url VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Tabel Approval Logs untuk tracking persetujuan
CREATE TABLE approval_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    document_id INT NOT NULL,
    user_id INT NOT NULL,
    action ENUM('submitted', 'approved', 'rejected', 'edited') NOT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Tabel Settings untuk konfigurasi aplikasi
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default settings
INSERT INTO settings (setting_key, setting_value, description) VALUES
('school_name', 'SMAN 1 Tanjungbalai', 'Nama Sekolah'),
('school_address', 'Tanjungbalai, Sumatera Utara', 'Alamat Sekolah'),
('app_name', 'Sistem Penandatanganan Digital SMAN 1 Tanjungbalai', 'Nama Aplikasi'),
('app_version', '1.0.0', 'Versi Aplikasi'),
('developer_name', 'T. Rifaldo Sany', 'Nama Pengembang');

-- Create indexes for better performance
CREATE INDEX idx_documents_status ON documents(status);
CREATE INDEX idx_documents_created_by ON documents(created_by);
CREATE INDEX idx_documents_number ON documents(document_number);
CREATE INDEX idx_approval_logs_document ON approval_logs(document_id);

