-- THIS YOU V2.0 reference DDL.
-- Canonical upgrade path remains /admin/migrate.php (migration 9 in lib/migrations.php).

CREATE TABLE IF NOT EXISTS magazine_instances (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_issue_id INT UNSIGNED NOT NULL,
    issue_id INT UNSIGNED NOT NULL,
    reader_user_id INT UNSIGNED NOT NULL,
    customer_user_id INT UNSIGNED NULL,
    source_type ENUM('order','subscription','manual','gift') NOT NULL DEFAULT 'manual',
    source_order_id VARCHAR(190) NULL,
    source_subscription_id VARCHAR(190) NULL,
    status ENUM('new','in_production','ready','active','pdf_ready','sent_to_print','printed','shipped','failed','cancelled') NOT NULL DEFAULT 'new',
    active_revision INT UNSIGNED NOT NULL DEFAULT 0,
    activated_at DATETIME NULL,
    pdf_interior_path VARCHAR(700) NULL,
    pdf_cover_path VARCHAR(700) NULL,
    pdf_generated_at DATETIME NULL,
    print_provider VARCHAR(100) NULL,
    print_job_id VARCHAR(190) NULL,
    print_submitted_at DATETIME NULL,
    tracking VARCHAR(255) NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY uq_magazine_user_issue (user_issue_id),
    KEY idx_magazine_issue (issue_id),
    KEY idx_magazine_reader (reader_user_id),
    KEY idx_magazine_status (status),
    KEY idx_magazine_source (source_type),
    CONSTRAINT fk_magazine_user_issue FOREIGN KEY (user_issue_id) REFERENCES user_issues(id) ON DELETE CASCADE,
    CONSTRAINT fk_magazine_issue FOREIGN KEY (issue_id) REFERENCES issues(id) ON DELETE CASCADE,
    CONSTRAINT fk_magazine_reader FOREIGN KEY (reader_user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_magazine_customer FOREIGN KEY (customer_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS chapter_generations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    magazine_instance_id INT UNSIGNED NOT NULL,
    chapter_id INT UNSIGNED NOT NULL,
    chapter_number INT UNSIGNED NOT NULL,
    version INT UNSIGNED NOT NULL,
    source VARCHAR(80) NOT NULL DEFAULT 'custom_gpt',
    status ENUM('ready','failed') NOT NULL DEFAULT 'ready',
    hero_path VARCHAR(700) NULL,
    package_path VARCHAR(900) NULL,
    result_json LONGTEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    UNIQUE KEY uq_chapter_generation_version (magazine_instance_id,chapter_id,version),
    KEY idx_chapter_generation_active (magazine_instance_id,chapter_id,is_active),
    CONSTRAINT fk_chapter_generation_magazine FOREIGN KEY (magazine_instance_id) REFERENCES magazine_instances(id) ON DELETE CASCADE,
    CONSTRAINT fk_chapter_generation_chapter FOREIGN KEY (chapter_id) REFERENCES issue_chapters(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS magazine_revisions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    magazine_instance_id INT UNSIGNED NOT NULL,
    revision_number INT UNSIGNED NOT NULL,
    snapshot_json LONGTEXT NOT NULL,
    created_at DATETIME NOT NULL,
    UNIQUE KEY uq_magazine_revision (magazine_instance_id,revision_number),
    CONSTRAINT fk_magazine_revision_magazine FOREIGN KEY (magazine_instance_id) REFERENCES magazine_instances(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
