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

CREATE TABLE users(
 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 name VARCHAR(120) NOT NULL,
 email VARCHAR(190) NOT NULL,
 password_hash VARCHAR(255) NOT NULL,
 is_admin TINYINT(1) NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL,
 PRIMARY KEY(id), UNIQUE KEY uq_email(email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE hosts(
 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 name VARCHAR(80) NOT NULL,
 adapter VARCHAR(80) NOT NULL,
 enabled TINYINT(1) NOT NULL DEFAULT 0,
 priority INT NOT NULL DEFAULT 100,
 created_at DATETIME NOT NULL,
 PRIMARY KEY(id), UNIQUE KEY uq_name(name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE files(
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 user_id INT UNSIGNED NOT NULL,
 source_url TEXT NOT NULL,
 filename VARCHAR(255) NULL,
 filesize BIGINT UNSIGNED NULL,
 mime_type VARCHAR(150) NULL,
 status VARCHAR(30) NOT NULL DEFAULT 'queued',
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 PRIMARY KEY(id), KEY idx_user(user_id), KEY idx_status(status),
 CONSTRAINT fk_files_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE upload_jobs(
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 file_id BIGINT UNSIGNED NOT NULL,
 host_id INT UNSIGNED NOT NULL,
 status VARCHAR(30) NOT NULL DEFAULT 'pending',
 attempts INT NOT NULL DEFAULT 0,
 download_url TEXT NULL,
 error_code VARCHAR(80) NULL,
 error_message TEXT NULL,
 started_at DATETIME NULL,
 completed_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 PRIMARY KEY(id),
 KEY idx_job(status,created_at),
 KEY idx_job_updated(status,updated_at),
 KEY idx_file(file_id),
 CONSTRAINT fk_jobs_file FOREIGN KEY(file_id) REFERENCES files(id) ON DELETE CASCADE,
 CONSTRAINT fk_jobs_host FOREIGN KEY(host_id) REFERENCES hosts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE upload_logs(
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 job_id BIGINT UNSIGNED NULL,
 level VARCHAR(20) NOT NULL,
 error_code VARCHAR(80) NULL,
 message TEXT NOT NULL,
 raw_response TEXT NULL,
 created_at DATETIME NOT NULL,
 PRIMARY KEY(id),
 KEY idx_logs_created(created_at),
 KEY idx_logs_job(job_id),
 CONSTRAINT fk_logs_job FOREIGN KEY(job_id) REFERENCES upload_jobs(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE custom_mirrors(
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 file_id BIGINT UNSIGNED NOT NULL,
 host_name VARCHAR(120) NOT NULL,
 download_url TEXT NOT NULL,
 position INT NOT NULL DEFAULT 1,
 enabled TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 PRIMARY KEY(id),
 KEY idx_custom_file(file_id),
 KEY idx_custom_position(file_id,position),
 CONSTRAINT fk_custom_file FOREIGN KEY(file_id) REFERENCES files(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE settings(
 setting_key VARCHAR(100) NOT NULL,
 setting_value TEXT NULL,
 updated_at DATETIME NOT NULL,
 PRIMARY KEY(setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO settings(setting_key,setting_value,updated_at) VALUES
('site_name','HostListing',NOW()),
('logo_url','',NOW()),
('mobile_logo_url','',NOW()),
('favicon_url','',NOW());

INSERT INTO hosts(name,adapter,enabled,priority,created_at) VALUES
('GoFile','gofile',0,10,NOW()),
('1fichier','fichier',0,20,NOW());