-- =====================================================
-- DATABASE SCHEMA CHO HỆ THỐNG QUẢN LÝ TÀI LIỆU PHÁP LUẬT
-- =====================================================

-- Tạo database (nếu chưa có)
CREATE DATABASE IF NOT EXISTS kienthuc_luathinhsu 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE kienthuc_luathinhsu;

-- =====================================================
-- BẢNG USERS - Quản lý người dùng
-- =====================================================
CREATE TABLE IF NOT EXISTS users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(255),
    phone VARCHAR(20),
    role ENUM('admin', 'user', 'moderator') DEFAULT 'user',
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- BẢNG DOCUMENT_CATEGORIES - Danh mục loại văn bản
-- =====================================================
CREATE TABLE IF NOT EXISTS document_categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(255) NOT NULL,
    category_code VARCHAR(50) UNIQUE,
    description TEXT,
    parent_category_id INT NULL,
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_category_id) REFERENCES document_categories(category_id) ON DELETE SET NULL,
    INDEX idx_category_code (category_code),
    INDEX idx_parent_category (parent_category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- BẢNG DOCUMENTS - Quản lý tài liệu tổng hợp
-- =====================================================
CREATE TABLE IF NOT EXISTS documents (
    document_id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Thông tin cơ bản
    document_title VARCHAR(500) NOT NULL,
    document_number VARCHAR(100),
    document_code VARCHAR(100) UNIQUE,
    
    -- Phân loại
    category_id INT,
    document_type ENUM(
        'van_ban_phap_luat',      -- Văn bản pháp luật
        'thong_tu',                -- Thông tư
        'nghi_dinh',               -- Nghị định
        'quyet_dinh',              -- Quyết định
        'chi_thi',                 -- Chỉ thị
        'huong_dan',               -- Hướng dẫn
        'tai_lieu_tham_khao',      -- Tài liệu tham khảo
        'bieu_mau',                -- Biểu mẫu
        'khac'                     -- Khác
    ) NOT NULL DEFAULT 'tai_lieu_tham_khao',
    
    -- Thông tin file
    file_name VARCHAR(255) NOT NULL,
    original_file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_url VARCHAR(1000) NOT NULL,
    file_size BIGINT COMMENT 'Kích thước file (bytes)',
    file_format VARCHAR(50) COMMENT 'pdf, doc, docx, xls, xlsx, etc.',
    mime_type VARCHAR(100),
    
    -- Mô tả và nội dung
    description TEXT,
    keywords VARCHAR(500) COMMENT 'Từ khóa tìm kiếm, phân cách bằng dấu phẩy',
    summary TEXT COMMENT 'Tóm tắt nội dung',
    
    -- Thông tin ban hành
    issuing_agency VARCHAR(255) COMMENT 'Cơ quan ban hành',
    issuing_date DATE COMMENT 'Ngày ban hành',
    effective_date DATE COMMENT 'Ngày có hiệu lực',
    expiry_date DATE COMMENT 'Ngày hết hiệu lực',
    
    -- Trạng thái
    status ENUM('draft', 'published', 'archived', 'deleted') DEFAULT 'published',
    is_public BOOLEAN DEFAULT TRUE COMMENT 'Công khai hay riêng tư',
    is_featured BOOLEAN DEFAULT FALSE COMMENT 'Tài liệu nổi bật',
    display_order INT DEFAULT 0 COMMENT 'Thứ tự hiển thị',
    
    -- Thống kê
    view_count INT DEFAULT 0,
    download_count INT DEFAULT 0,
    
    -- Người quản lý
    uploaded_by INT NOT NULL,
    approved_by INT NULL,
    
    -- Thời gian
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    published_at TIMESTAMP NULL,
    
    -- Foreign Keys
    FOREIGN KEY (category_id) REFERENCES document_categories(category_id) ON DELETE SET NULL,
    FOREIGN KEY (uploaded_by) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (approved_by) REFERENCES users(user_id) ON DELETE SET NULL,
    
    -- Indexes
    INDEX idx_document_code (document_code),
    INDEX idx_document_number (document_number),
    INDEX idx_category (category_id),
    INDEX idx_document_type (document_type),
    INDEX idx_status (status),
    INDEX idx_uploaded_by (uploaded_by),
    INDEX idx_created_at (created_at),
    INDEX idx_issuing_date (issuing_date),
    FULLTEXT INDEX idx_fulltext_search (document_title, description, keywords, summary)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- BẢNG DOCUMENT_TAGS - Tags cho tài liệu
-- =====================================================
CREATE TABLE IF NOT EXISTS document_tags (
    tag_id INT AUTO_INCREMENT PRIMARY KEY,
    tag_name VARCHAR(100) NOT NULL UNIQUE,
    tag_slug VARCHAR(100) NOT NULL UNIQUE,
    usage_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tag_slug (tag_slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- BẢNG DOCUMENT_TAG_RELATIONS - Quan hệ tài liệu - tag
-- =====================================================
CREATE TABLE IF NOT EXISTS document_tag_relations (
    relation_id INT AUTO_INCREMENT PRIMARY KEY,
    document_id INT NOT NULL,
    tag_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (document_id) REFERENCES documents(document_id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES document_tags(tag_id) ON DELETE CASCADE,
    UNIQUE KEY unique_document_tag (document_id, tag_id),
    INDEX idx_document (document_id),
    INDEX idx_tag (tag_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- BẢNG DOCUMENT_VERSIONS - Quản lý phiên bản tài liệu
-- =====================================================
CREATE TABLE IF NOT EXISTS document_versions (
    version_id INT AUTO_INCREMENT PRIMARY KEY,
    document_id INT NOT NULL,
    version_number VARCHAR(20) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_url VARCHAR(1000) NOT NULL,
    file_size BIGINT,
    change_notes TEXT,
    uploaded_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (document_id) REFERENCES documents(document_id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(user_id) ON DELETE CASCADE,
    INDEX idx_document (document_id),
    INDEX idx_version (version_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- BẢNG DOCUMENT_COMMENTS - Bình luận tài liệu
-- =====================================================
CREATE TABLE IF NOT EXISTS document_comments (
    comment_id INT AUTO_INCREMENT PRIMARY KEY,
    document_id INT NOT NULL,
    user_id INT NOT NULL,
    parent_comment_id INT NULL,
    comment_text TEXT NOT NULL,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'approved',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (document_id) REFERENCES documents(document_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (parent_comment_id) REFERENCES document_comments(comment_id) ON DELETE CASCADE,
    INDEX idx_document (document_id),
    INDEX idx_user (user_id),
    INDEX idx_parent (parent_comment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- BẢNG DOCUMENT_RATINGS - Đánh giá tài liệu
-- =====================================================
CREATE TABLE IF NOT EXISTS document_ratings (
    rating_id INT AUTO_INCREMENT PRIMARY KEY,
    document_id INT NOT NULL,
    user_id INT NOT NULL,
    rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    review_text TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (document_id) REFERENCES documents(document_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_document_rating (document_id, user_id),
    INDEX idx_document (document_id),
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- BẢNG DOCUMENT_ACCESS_LOG - Lịch sử truy cập tài liệu
-- =====================================================
CREATE TABLE IF NOT EXISTS document_access_log (
    log_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    document_id INT NOT NULL,
    user_id INT NULL,
    action_type ENUM('view', 'download', 'share', 'print') NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    access_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (document_id) REFERENCES documents(document_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL,
    INDEX idx_document (document_id),
    INDEX idx_user (user_id),
    INDEX idx_action (action_type),
    INDEX idx_access_time (access_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- BẢNG USER_FAVORITES - Tài liệu yêu thích
-- =====================================================
CREATE TABLE IF NOT EXISTS user_favorites (
    favorite_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    document_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (document_id) REFERENCES documents(document_id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_favorite (user_id, document_id),
    INDEX idx_user (user_id),
    INDEX idx_document (document_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- DỮ LIỆU MẪU - CATEGORIES
-- =====================================================
INSERT INTO document_categories (category_name, category_code, description, display_order) VALUES
('Luật', 'LUAT', 'Các văn bản luật do Quốc hội ban hành', 1),
('Nghị định', 'NGHI_DINH', 'Các nghị định của Chính phủ', 2),
('Thông tư', 'THONG_TU', 'Các thông tư hướng dẫn thi hành', 3),
('Quyết định', 'QUYET_DINH', 'Các quyết định hành chính', 4),
('Chỉ thị', 'CHI_THI', 'Các chỉ thị của cơ quan nhà nước', 5),
('Biểu mẫu', 'BIEU_MAU', 'Các biểu mẫu pháp lý', 6),
('Tài liệu tham khảo', 'TAI_LIEU', 'Tài liệu nghiên cứu và tham khảo', 7);

-- =====================================================
-- DỮ LIỆU MẪU - USER ADMIN
-- =====================================================
-- Password: admin123 (đã hash bằng bcrypt)
INSERT INTO users (username, email, password_hash, full_name, role, status) VALUES
('admin', 'admin@luathinhsu.vn', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Quản trị viên', 'admin', 'active');

-- =====================================================
-- VIEWS - Tạo các view hữu ích
-- =====================================================

-- View: Thống kê tài liệu theo danh mục
CREATE OR REPLACE VIEW v_document_stats_by_category AS
SELECT 
    c.category_id,
    c.category_name,
    c.category_code,
    COUNT(d.document_id) as total_documents,
    SUM(d.view_count) as total_views,
    SUM(d.download_count) as total_downloads,
    AVG(COALESCE(r.avg_rating, 0)) as avg_rating
FROM document_categories c
LEFT JOIN documents d ON c.category_id = d.category_id AND d.status = 'published'
LEFT JOIN (
    SELECT document_id, AVG(rating) as avg_rating
    FROM document_ratings
    GROUP BY document_id
) r ON d.document_id = r.document_id
GROUP BY c.category_id, c.category_name, c.category_code
ORDER BY c.display_order;

-- View: Tài liệu phổ biến nhất
CREATE OR REPLACE VIEW v_popular_documents AS
SELECT 
    d.document_id,
    d.document_title,
    d.document_code,
    d.document_type,
    c.category_name,
    d.file_format,
    d.view_count,
    d.download_count,
    d.created_at,
    u.full_name as uploaded_by_name,
    COALESCE(AVG(r.rating), 0) as avg_rating,
    COUNT(DISTINCT r.rating_id) as rating_count
FROM documents d
LEFT JOIN document_categories c ON d.category_id = c.category_id
LEFT JOIN users u ON d.uploaded_by = u.user_id
LEFT JOIN document_ratings r ON d.document_id = r.document_id
WHERE d.status = 'published' AND d.is_public = TRUE
GROUP BY d.document_id
ORDER BY (d.view_count + d.download_count * 2) DESC
LIMIT 50;

-- View: Tài liệu mới nhất
CREATE OR REPLACE VIEW v_recent_documents AS
SELECT 
    d.document_id,
    d.document_title,
    d.document_code,
    d.document_type,
    c.category_name,
    d.file_format,
    d.file_size,
    d.file_url,
    d.description,
    d.created_at,
    u.full_name as uploaded_by_name
FROM documents d
LEFT JOIN document_categories c ON d.category_id = c.category_id
LEFT JOIN users u ON d.uploaded_by = u.user_id
WHERE d.status = 'published' AND d.is_public = TRUE
ORDER BY d.created_at DESC
LIMIT 100;

-- =====================================================
-- STORED PROCEDURES
-- =====================================================

-- Procedure: Tăng view count
DELIMITER //
CREATE PROCEDURE sp_increment_view_count(IN p_document_id INT)
BEGIN
    UPDATE documents 
    SET view_count = view_count + 1 
    WHERE document_id = p_document_id;
END //
DELIMITER ;

-- Procedure: Tăng download count
DELIMITER //
CREATE PROCEDURE sp_increment_download_count(IN p_document_id INT)
BEGIN
    UPDATE documents 
    SET download_count = download_count + 1 
    WHERE document_id = p_document_id;
END //
DELIMITER ;

-- Procedure: Lấy thống kê tổng quan
DELIMITER //
CREATE PROCEDURE sp_get_dashboard_stats()
BEGIN
    SELECT 
        (SELECT COUNT(*) FROM documents WHERE status = 'published') as total_documents,
        (SELECT COUNT(*) FROM users WHERE status = 'active') as total_users,
        (SELECT SUM(view_count) FROM documents) as total_views,
        (SELECT SUM(download_count) FROM documents) as total_downloads,
        (SELECT COUNT(*) FROM documents WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)) as new_documents_this_week,
        (SELECT COUNT(*) FROM document_access_log WHERE access_time >= DATE_SUB(NOW(), INTERVAL 24 HOUR)) as activities_last_24h;
END //
DELIMITER ;

-- =====================================================
-- TRIGGERS
-- =====================================================

-- Trigger: Tự động cập nhật usage_count cho tags
DELIMITER //
CREATE TRIGGER tr_update_tag_usage_after_insert
AFTER INSERT ON document_tag_relations
FOR EACH ROW
BEGIN
    UPDATE document_tags 
    SET usage_count = usage_count + 1 
    WHERE tag_id = NEW.tag_id;
END //
DELIMITER ;

DELIMITER //
CREATE TRIGGER tr_update_tag_usage_after_delete
AFTER DELETE ON document_tag_relations
FOR EACH ROW
BEGIN
    UPDATE document_tags 
    SET usage_count = usage_count - 1 
    WHERE tag_id = OLD.tag_id;
END //
DELIMITER ;

-- =====================================================
-- INDEXES BỔ SUNG ĐỂ TỐI ƯU PERFORMANCE
-- =====================================================

-- Composite indexes cho các truy vấn phổ biến
ALTER TABLE documents ADD INDEX idx_status_public_created (status, is_public, created_at DESC);
ALTER TABLE documents ADD INDEX idx_category_status (category_id, status);
ALTER TABLE document_access_log ADD INDEX idx_document_time (document_id, access_time DESC);

-- =====================================================
-- COMMENTS
-- =====================================================
ALTER TABLE documents COMMENT = 'Bảng chính lưu trữ thông tin tài liệu pháp luật';
ALTER TABLE document_categories COMMENT = 'Danh mục phân loại tài liệu';
ALTER TABLE document_tags COMMENT = 'Tags để gắn nhãn tài liệu';
ALTER TABLE document_access_log COMMENT = 'Lịch sử truy cập và tải tài liệu';