CREATE TABLE admins (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(80) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE projects (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(160) NOT NULL, slug VARCHAR(160) NOT NULL UNIQUE, status ENUM('draft','published','unpublished') NOT NULL DEFAULT 'draft', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE viewpoints (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, project_id INT UNSIGNED NOT NULL, name VARCHAR(160) NOT NULL, default_scenario_id INT UNSIGNED NULL, sort_order INT NOT NULL DEFAULT 0, FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE scenarios (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, viewpoint_id INT UNSIGNED NOT NULL, name VARCHAR(160) NOT NULL, enabled TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0, FOREIGN KEY(viewpoint_id) REFERENCES viewpoints(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE panoramas (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, scenario_id INT UNSIGNED NOT NULL, storage_name VARCHAR(255) NOT NULL, original_name VARCHAR(255) NOT NULL, format ENUM('mono','tb','sbs') NOT NULL, swap_eyes TINYINT(1) NOT NULL DEFAULT 0, mime_type VARCHAR(80) NOT NULL, byte_size BIGINT UNSIGNED NOT NULL, width INT UNSIGNED NULL, height INT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(scenario_id) REFERENCES scenarios(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE hotspots (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, viewpoint_id INT UNSIGNED NOT NULL, destination_viewpoint_id INT UNSIGNED NOT NULL, label VARCHAR(160) NOT NULL, yaw DECIMAL(9,6) NOT NULL, pitch DECIMAL(9,6) NOT NULL, sort_order INT NOT NULL DEFAULT 0, FOREIGN KEY(viewpoint_id) REFERENCES viewpoints(id) ON DELETE CASCADE, FOREIGN KEY(destination_viewpoint_id) REFERENCES viewpoints(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE viewpoints ADD CONSTRAINT fk_viewpoint_default_scenario FOREIGN KEY(default_scenario_id) REFERENCES scenarios(id) ON DELETE SET NULL;