CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    fiscal_code VARCHAR(16) NOT NULL,
    birth_date DATE NULL,
    birth_place VARCHAR(150) NULL,
    phone VARCHAR(30) NULL,
    points INT NOT NULL DEFAULT 0,
    banned BOOLEAN NOT NULL DEFAULT FALSE,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY users_email_unique (email),
    UNIQUE KEY users_fiscal_code_unique (fiscal_code)
);

CREATE TABLE IF NOT EXISTS roles (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    description TEXT NULL,
    UNIQUE KEY roles_name_unique (name)
);

CREATE TABLE IF NOT EXISTS permissions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT NULL,
    UNIQUE KEY permissions_name_unique (name)
);

CREATE TABLE IF NOT EXISTS role_permissions (
    role_id BIGINT UNSIGNED NOT NULL,
    permission_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    CONSTRAINT role_permissions_role_fk FOREIGN KEY (role_id) REFERENCES roles(id),
    CONSTRAINT role_permissions_permission_fk FOREIGN KEY (permission_id) REFERENCES permissions(id)
);

CREATE TABLE IF NOT EXISTS user_roles (
    user_id BIGINT UNSIGNED NOT NULL,
    role_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (user_id, role_id),
    CONSTRAINT user_roles_user_fk FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT user_roles_role_fk FOREIGN KEY (role_id) REFERENCES roles(id)
);

CREATE TABLE IF NOT EXISTS otp_challenges (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    code_hash VARCHAR(255) NOT NULL,
    expires_at DATETIME NOT NULL,
    consumed_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT otp_challenges_user_fk FOREIGN KEY (user_id) REFERENCES users(id),
    KEY otp_challenges_user_expires_idx (user_id, expires_at)
);

CREATE TABLE IF NOT EXISTS api_tokens (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    expires_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    revoked_at DATETIME NULL,
    UNIQUE KEY api_tokens_hash_unique (token_hash),
    CONSTRAINT api_tokens_user_fk FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS report_categories (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    description TEXT NULL,
    active BOOLEAN NOT NULL DEFAULT TRUE,
    UNIQUE KEY report_categories_name_unique (name)
);

CREATE TABLE IF NOT EXISTS areas (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    description TEXT NULL,
    boundary_geojson JSON NOT NULL,
    UNIQUE KEY areas_name_unique (name)
);

CREATE TABLE IF NOT EXISTS team_categories (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    description TEXT NULL,
    UNIQUE KEY team_categories_name_unique (name)
);

CREATE TABLE IF NOT EXISTS teams (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    team_category_id BIGINT UNSIGNED NOT NULL,
    area_id BIGINT UNSIGNED NULL,
    name VARCHAR(120) NOT NULL,
    description TEXT NULL,
    active BOOLEAN NOT NULL DEFAULT TRUE,
    UNIQUE KEY teams_name_unique (name),
    CONSTRAINT teams_category_fk FOREIGN KEY (team_category_id) REFERENCES team_categories(id),
    CONSTRAINT teams_area_fk FOREIGN KEY (area_id) REFERENCES areas(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS reports (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    category_id BIGINT UNSIGNED NOT NULL,
    area_id BIGINT UNSIGNED NULL,
    latitude DECIMAL(10, 7) NOT NULL,
    longitude DECIMAL(10, 7) NOT NULL,
    address VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    status ENUM('OPEN','UNDER_REVIEW','ASSIGNED','IN_PROGRESS','RESOLVED','REJECTED','CLOSED') NOT NULL DEFAULT 'OPEN',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT reports_user_fk FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT reports_category_fk FOREIGN KEY (category_id) REFERENCES report_categories(id),
    CONSTRAINT reports_area_fk FOREIGN KEY (area_id) REFERENCES areas(id) ON DELETE SET NULL,
    KEY reports_status_idx (status),
    KEY reports_category_idx (category_id),
    KEY reports_area_idx (area_id)
);

CREATE TABLE IF NOT EXISTS report_status_history (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    report_id BIGINT UNSIGNED NOT NULL,
    old_status VARCHAR(30) NULL,
    new_status VARCHAR(30) NOT NULL,
    changed_by_user_id BIGINT UNSIGNED NULL,
    note TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT status_history_report_fk FOREIGN KEY (report_id) REFERENCES reports(id),
    CONSTRAINT status_history_user_fk FOREIGN KEY (changed_by_user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS assignments (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    report_id BIGINT UNSIGNED NOT NULL,
    team_id BIGINT UNSIGNED NOT NULL,
    assigned_by_user_id BIGINT UNSIGNED NOT NULL,
    assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ended_at DATETIME NULL,
    CONSTRAINT assignments_report_fk FOREIGN KEY (report_id) REFERENCES reports(id),
    CONSTRAINT assignments_team_fk FOREIGN KEY (team_id) REFERENCES teams(id),
    CONSTRAINT assignments_user_fk FOREIGN KEY (assigned_by_user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS report_photos (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    report_id BIGINT UNSIGNED NOT NULL,
    url VARCHAR(500) NOT NULL,
    uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT report_photos_report_fk FOREIGN KEY (report_id) REFERENCES reports(id)
);

CREATE TABLE IF NOT EXISTS messages (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    report_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    body TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT messages_report_fk FOREIGN KEY (report_id) REFERENCES reports(id),
    CONSTRAINT messages_user_fk FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS feedback (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    report_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    rating TINYINT UNSIGNED NOT NULL,
    comment TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY feedback_report_unique (report_id),
    CONSTRAINT feedback_report_fk FOREIGN KEY (report_id) REFERENCES reports(id),
    CONSTRAINT feedback_user_fk FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT feedback_rating_check CHECK (rating BETWEEN 1 AND 5)
);

CREATE TABLE IF NOT EXISTS reward_events (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    report_id BIGINT UNSIGNED NULL,
    points INT NOT NULL,
    reason VARCHAR(120) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT reward_events_user_fk FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT reward_events_report_fk FOREIGN KEY (report_id) REFERENCES reports(id)
);

INSERT IGNORE INTO roles (name, description) VALUES
    ('admin', 'System administrator'),
    ('operator', 'Municipal operator'),
    ('citizen', 'Registered citizen');

INSERT IGNORE INTO permissions (name, description) VALUES
    ('reports.read_all', 'Read every report'),
    ('reports.manage', 'Manage report state and assignments'),
    ('admin.manage', 'Manage administrative data');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p
WHERE r.name = 'admin';

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p
WHERE r.name = 'operator' AND p.name IN ('reports.read_all', 'reports.manage');

INSERT IGNORE INTO report_categories (name, description) VALUES
    ('Buche stradali', 'Problemi relativi al manto stradale'),
    ('Illuminazione guasta', 'Lampioni o impianti di illuminazione non funzionanti'),
    ('Rifiuti abbandonati', 'Rifiuti fuori dalle aree autorizzate'),
    ('Altro', 'Altri disservizi urbani');

INSERT IGNORE INTO team_categories (name, description) VALUES
    ('Manutenzione strade', 'Squadre per interventi stradali'),
    ('Illuminazione pubblica', 'Squadre per rete elettrica e lampioni'),
    ('Igiene urbana', 'Squadre per rifiuti e decoro');

INSERT IGNORE INTO users (
    first_name,
    last_name,
    email,
    fiscal_code,
    phone
) VALUES (
    'Vito',
    'Leccese',
    'admin@visionebari.it',
    'LCCVTI62M02A662Q',
    '380 6180216'
);

INSERT IGNORE INTO user_roles (user_id, role_id)
SELECT u.id, r.id FROM users u JOIN roles r
WHERE u.email = 'admin@visionebari.it' AND r.name = 'admin';