SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS afilww_audit_logs;
DROP TABLE IF EXISTS afilww_mail_logs;
DROP TABLE IF EXISTS afilww_email_codes;
DROP TABLE IF EXISTS afilww_acceptance_checkboxes;
DROP TABLE IF EXISTS afilww_acceptance_documents;
DROP TABLE IF EXISTS afilww_acceptances;
DROP TABLE IF EXISTS afilww_acceptance_sessions;
DROP TABLE IF EXISTS afilww_attachments;
DROP TABLE IF EXISTS afilww_document_versions;
DROP TABLE IF EXISTS afilww_documents;
DROP TABLE IF EXISTS afilww_invitations;
DROP TABLE IF EXISTS afilww_admins;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE afilww_admins (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(190) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(120) NOT NULL DEFAULT 'Admin',
    role ENUM('superadmin', 'admin') NOT NULL DEFAULT 'admin',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_afilww_admins_email (email),
    KEY idx_afilww_admins_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE afilww_invitations (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(190) NOT NULL,
    token_hash CHAR(64) NOT NULL,
    token_last4 CHAR(4) NULL,
    status ENUM('pending', 'started', 'accepted', 'revoked', 'expired') NOT NULL DEFAULT 'pending',

    first_name VARCHAR(120) NULL,
    last_name VARCHAR(120) NULL,
    birth_date DATE NULL,
    phone VARCHAR(40) NULL,

    created_by_admin_id BIGINT UNSIGNED NULL,
    revoked_by_admin_id BIGINT UNSIGNED NULL,
    revocation_reason VARCHAR(500) NULL,

    expires_at DATETIME NULL,
    started_at DATETIME NULL,
    accepted_at DATETIME NULL,
    revoked_at DATETIME NULL,
    expired_at DATETIME NULL,

    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,

    PRIMARY KEY (id),
    UNIQUE KEY uq_afilww_invitations_token_hash (token_hash),
    KEY idx_afilww_invitations_email (email),
    KEY idx_afilww_invitations_status (status),
    KEY idx_afilww_invitations_expires_at (expires_at),
    CONSTRAINT fk_afilww_invitations_created_by_admin
        FOREIGN KEY (created_by_admin_id) REFERENCES afilww_admins(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_afilww_invitations_revoked_by_admin
        FOREIGN KEY (revoked_by_admin_id) REFERENCES afilww_admins(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE afilww_documents (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(190) NOT NULL,
    type ENUM('contract', 'terms', 'rodo', 'other') NOT NULL DEFAULT 'other',
    status ENUM('draft', 'active', 'archived') NOT NULL DEFAULT 'draft',
    sort_order INT NOT NULL DEFAULT 0,
    created_by_admin_id BIGINT UNSIGNED NULL,
    updated_by_admin_id BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_afilww_documents_slug (slug),
    KEY idx_afilww_documents_type_status (type, status),
    KEY idx_afilww_documents_sort_order (sort_order),
    CONSTRAINT fk_afilww_documents_created_by_admin
        FOREIGN KEY (created_by_admin_id) REFERENCES afilww_admins(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_afilww_documents_updated_by_admin
        FOREIGN KEY (updated_by_admin_id) REFERENCES afilww_admins(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE afilww_document_versions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    document_id BIGINT UNSIGNED NOT NULL,
    version_number INT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    html_content MEDIUMTEXT NOT NULL,
    plain_text MEDIUMTEXT NULL,
    snapshot_html_path VARCHAR(255) NULL,
    snapshot_pdf_path VARCHAR(255) NULL,
    sha256_hash CHAR(64) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 0,
    published_at DATETIME NULL,
    created_by_admin_id BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_afilww_document_versions_document_version (document_id, version_number),
    KEY idx_afilww_document_versions_active (document_id, is_active),
    KEY idx_afilww_document_versions_hash (sha256_hash),
    CONSTRAINT fk_afilww_document_versions_document
        FOREIGN KEY (document_id) REFERENCES afilww_documents(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_afilww_document_versions_created_by_admin
        FOREIGN KEY (created_by_admin_id) REFERENCES afilww_admins(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE afilww_attachments (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    stored_filename VARCHAR(255) NOT NULL,
    storage_path VARCHAR(255) NOT NULL,
    mime_type VARCHAR(120) NOT NULL,
    size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
    sha256_hash CHAR(64) NOT NULL,
    status ENUM('active', 'archived') NOT NULL DEFAULT 'active',
    uploaded_by_admin_id BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL,
    archived_at DATETIME NULL,
    PRIMARY KEY (id),
    KEY idx_afilww_attachments_status (status),
    KEY idx_afilww_attachments_hash (sha256_hash),
    CONSTRAINT fk_afilww_attachments_uploaded_by_admin
        FOREIGN KEY (uploaded_by_admin_id) REFERENCES afilww_admins(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE afilww_acceptance_sessions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL,
    invitation_id BIGINT UNSIGNED NOT NULL,
    email_code_id BIGINT UNSIGNED NULL,
    email VARCHAR(190) NOT NULL,
    status ENUM('started', 'email_code_sent', 'email_verified', 'accepted', 'cancelled', 'expired') NOT NULL DEFAULT 'started',
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(512) NULL,
    started_at DATETIME NOT NULL,
    email_verified_at DATETIME NULL,
    completed_at DATETIME NULL,
    expires_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_afilww_acceptance_sessions_uuid (uuid),
    KEY idx_afilww_acceptance_sessions_invitation (invitation_id),
    KEY idx_afilww_acceptance_sessions_email (email),
    KEY idx_afilww_acceptance_sessions_status (status),
    CONSTRAINT fk_afilww_acceptance_sessions_invitation
        FOREIGN KEY (invitation_id) REFERENCES afilww_invitations(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE afilww_acceptances (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    invitation_id BIGINT UNSIGNED NOT NULL,
    acceptance_session_id BIGINT UNSIGNED NOT NULL,
    status ENUM('accepted', 'cancelled', 'invalidated') NOT NULL DEFAULT 'accepted',

    first_name VARCHAR(120) NOT NULL,
    last_name VARCHAR(120) NOT NULL,
    birth_date DATE NOT NULL,
    email VARCHAR(190) NOT NULL,
    phone VARCHAR(40) NULL,

    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(512) NULL,

    email_code_confirmed TINYINT(1) NOT NULL DEFAULT 0,
    email_code_confirmed_at DATETIME NULL,

    accepted_at DATETIME NOT NULL,
    snapshot_html_path VARCHAR(255) NULL,
    snapshot_pdf_path VARCHAR(255) NULL,
    acceptance_hash CHAR(64) NOT NULL,
    created_at DATETIME NOT NULL,

    PRIMARY KEY (id),
    UNIQUE KEY uq_afilww_acceptances_invitation (invitation_id),
    UNIQUE KEY uq_afilww_acceptances_session (acceptance_session_id),
    KEY idx_afilww_acceptances_email (email),
    KEY idx_afilww_acceptances_status (status),
    KEY idx_afilww_acceptances_accepted_at (accepted_at),
    KEY idx_afilww_acceptances_hash (acceptance_hash),
    CONSTRAINT fk_afilww_acceptances_invitation
        FOREIGN KEY (invitation_id) REFERENCES afilww_invitations(id)
        ON DELETE RESTRICT,
    CONSTRAINT fk_afilww_acceptances_session
        FOREIGN KEY (acceptance_session_id) REFERENCES afilww_acceptance_sessions(id)
        ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE afilww_acceptance_documents (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    acceptance_id BIGINT UNSIGNED NOT NULL,
    document_id BIGINT UNSIGNED NOT NULL,
    document_version_id BIGINT UNSIGNED NOT NULL,
    document_type ENUM('contract', 'terms', 'rodo', 'other') NOT NULL DEFAULT 'other',
    title VARCHAR(255) NOT NULL,
    version_number INT UNSIGNED NOT NULL,
    sha256_hash CHAR(64) NOT NULL,
    snapshot_html_path VARCHAR(255) NULL,
    snapshot_pdf_path VARCHAR(255) NULL,
    accepted_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_afilww_acceptance_documents_version (acceptance_id, document_version_id),
    KEY idx_afilww_acceptance_documents_acceptance (acceptance_id),
    KEY idx_afilww_acceptance_documents_document (document_id),
    CONSTRAINT fk_afilww_acceptance_documents_acceptance
        FOREIGN KEY (acceptance_id) REFERENCES afilww_acceptances(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_afilww_acceptance_documents_document
        FOREIGN KEY (document_id) REFERENCES afilww_documents(id)
        ON DELETE RESTRICT,
    CONSTRAINT fk_afilww_acceptance_documents_version
        FOREIGN KEY (document_version_id) REFERENCES afilww_document_versions(id)
        ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE afilww_acceptance_checkboxes (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    acceptance_id BIGINT UNSIGNED NOT NULL,
    checkbox_key VARCHAR(120) NOT NULL,
    label TEXT NOT NULL,
    required TINYINT(1) NOT NULL DEFAULT 1,
    checked TINYINT(1) NOT NULL DEFAULT 1,
    checked_at DATETIME NOT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(512) NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_afilww_acceptance_checkboxes_key (acceptance_id, checkbox_key),
    KEY idx_afilww_acceptance_checkboxes_acceptance (acceptance_id),
    CONSTRAINT fk_afilww_acceptance_checkboxes_acceptance
        FOREIGN KEY (acceptance_id) REFERENCES afilww_acceptances(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE afilww_email_codes (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    invitation_id BIGINT UNSIGNED NULL,
    acceptance_session_id BIGINT UNSIGNED NULL,
    email VARCHAR(190) NOT NULL,
    purpose ENUM('acceptance_confirmation', 'admin_password_reset') NOT NULL DEFAULT 'acceptance_confirmation',
    code_hash VARCHAR(255) NOT NULL,
    status ENUM('pending', 'used', 'expired', 'cancelled') NOT NULL DEFAULT 'pending',
    attempts INT UNSIGNED NOT NULL DEFAULT 0,
    max_attempts INT UNSIGNED NOT NULL DEFAULT 5,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(512) NULL,
    sent_at DATETIME NULL,
    used_at DATETIME NULL,
    expires_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_afilww_email_codes_email_status (email, status),
    KEY idx_afilww_email_codes_invitation (invitation_id),
    KEY idx_afilww_email_codes_session (acceptance_session_id),
    KEY idx_afilww_email_codes_expires_at (expires_at),
    CONSTRAINT fk_afilww_email_codes_invitation
        FOREIGN KEY (invitation_id) REFERENCES afilww_invitations(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_afilww_email_codes_session
        FOREIGN KEY (acceptance_session_id) REFERENCES afilww_acceptance_sessions(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE afilww_acceptance_sessions
    ADD CONSTRAINT fk_afilww_acceptance_sessions_email_code
    FOREIGN KEY (email_code_id) REFERENCES afilww_email_codes(id)
    ON DELETE SET NULL;

CREATE TABLE afilww_mail_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    invitation_id BIGINT UNSIGNED NULL,
    acceptance_session_id BIGINT UNSIGNED NULL,
    recipient_email VARCHAR(190) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    template VARCHAR(120) NULL,
    purpose VARCHAR(120) NULL,
    status ENUM('queued', 'sent', 'failed') NOT NULL DEFAULT 'queued',
    provider_message_id VARCHAR(255) NULL,
    error_message TEXT NULL,
    meta_json JSON NULL,
    sent_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_afilww_mail_logs_recipient (recipient_email),
    KEY idx_afilww_mail_logs_status (status),
    KEY idx_afilww_mail_logs_created_at (created_at),
    CONSTRAINT fk_afilww_mail_logs_invitation
        FOREIGN KEY (invitation_id) REFERENCES afilww_invitations(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_afilww_mail_logs_session
        FOREIGN KEY (acceptance_session_id) REFERENCES afilww_acceptance_sessions(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE afilww_audit_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    admin_id BIGINT UNSIGNED NULL,
    invitation_id BIGINT UNSIGNED NULL,
    entity_type VARCHAR(80) NOT NULL,
    entity_id BIGINT UNSIGNED NULL,
    action VARCHAR(120) NOT NULL,
    message VARCHAR(500) NULL,
    context_json JSON NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(512) NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_afilww_audit_logs_admin (admin_id),
    KEY idx_afilww_audit_logs_invitation (invitation_id),
    KEY idx_afilww_audit_logs_entity (entity_type, entity_id),
    KEY idx_afilww_audit_logs_action (action),
    KEY idx_afilww_audit_logs_created_at (created_at),
    CONSTRAINT fk_afilww_audit_logs_admin
        FOREIGN KEY (admin_id) REFERENCES afilww_admins(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_afilww_audit_logs_invitation
        FOREIGN KEY (invitation_id) REFERENCES afilww_invitations(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;