CREATE DATABASE IF NOT EXISTS socmed CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE socmed;

CREATE TABLE IF NOT EXISTS admin (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    pass VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS audit_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    actor_id INT,
    actor_email VARCHAR(255),
    action VARCHAR(80),
    target_user_id INT,
    target_email VARCHAR(255),
    details VARCHAR(1000),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    report_id INT,
    user_id INT,
    comment TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS loginn (
    id INT AUTO_INCREMENT PRIMARY KEY,
    userr VARCHAR(50),
    pass VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS message_archives (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    other_user_id INT,
    archived_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sender_id INT,
    receiver_id INT,
    message VARCHAR(1000),
    is_read TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    type VARCHAR(50),
    message VARCHAR(500),
    report_id INT,
    is_read TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS report_issue_flags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    report_id INT,
    reporter_user_id INT,
    reason VARCHAR(100),
    details VARCHAR(1000),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    category VARCHAR(100),
    location VARCHAR(255),
    description TEXT,
    image_path VARCHAR(500),
    severity FLOAT DEFAULT 0,
    status VARCHAR(50) DEFAULT 'Pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    scheduled_date DATE,
    upvotes INT DEFAULT 0,
    witnesses INT DEFAULT 0,
    duplicates INT DEFAULT 0,
    assigned_dept VARCHAR(255),
    admin_response TEXT,
    photo_path VARCHAR(255),
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    hazard_tag VARCHAR(20),
    map_x FLOAT,
    map_y FLOAT
);

CREATE TABLE IF NOT EXISTS upvotes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    report_id INT,
    user_id INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_upvote (report_id, user_id)
);

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100),
    password VARCHAR(255),
    role VARCHAR(20) DEFAULT 'user',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    name VARCHAR(255),
    profile_photo VARCHAR(500),
    status VARCHAR(20) DEFAULT 'active',
    username VARCHAR(100),
    passwordhash VARCHAR(255),
    last_active DATETIME
);

CREATE TABLE IF NOT EXISTS severity_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    config_key VARCHAR(100) UNIQUE,
    config_value FLOAT DEFAULT 0
);

INSERT IGNORE INTO severity_config (config_key, config_value) VALUES
('upvote_weight', 0.04),
('duplicate_weight', 0.8),
('witness_weight', 0.3),
('main_road_multiplier', 1.4),
('normal_road_multiplier', 1.0);
