CREATE TABLE IF NOT EXISTS aau_clubs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    lead_id VARCHAR(64) NULL,
    status ENUM('pending', 'in_progress', 'done', 'no_site_found', 'needs_review', 'failed', 'skipped') NOT NULL DEFAULT 'pending',
    attempt_count INT UNSIGNED NOT NULL DEFAULT 0,
    claimed_by VARCHAR(128) NULL,
    claimed_at DATETIME NULL,
    lease_expires_at DATETIME NULL,
    last_attempt_at DATETIME NULL,
    completed_at DATETIME NULL,
    club_name VARCHAR(255) NOT NULL,
    address VARCHAR(512) NULL,
    city VARCHAR(160) NULL,
    state VARCHAR(160) NULL,
    postal_code VARCHAR(32) NULL,
    district VARCHAR(160) NULL,
    sports VARCHAR(512) NULL,
    aau_source_url TEXT NULL,
    source_file VARCHAR(512) NULL,
    source_row VARCHAR(64) NULL,
    source_key TEXT NULL,
    source_hash CHAR(64) NOT NULL,
    website_domain VARCHAR(255) NULL,
    website_url TEXT NULL,
    website_confidence VARCHAR(32) NULL,
    contacts_workbook VARCHAR(512) NULL,
    notes TEXT NULL,
    last_error TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_aau_clubs_lead_id (lead_id),
    UNIQUE KEY uq_aau_clubs_source_hash (source_hash),
    KEY idx_aau_clubs_claimable (status, lease_expires_at, id),
    KEY idx_aau_clubs_completed_at (completed_at),
    KEY idx_aau_clubs_claimed_by (claimed_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS aau_website_candidates (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    club_id BIGINT UNSIGNED NOT NULL,
    submitted_by VARCHAR(128) NOT NULL,
    domain VARCHAR(255) NULL,
    url TEXT NULL,
    confidence TINYINT UNSIGNED NULL,
    status ENUM('candidate', 'preferred', 'rejected', 'needs_review', 'not_found') NOT NULL DEFAULT 'candidate',
    source_url TEXT NULL,
    search_query TEXT NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_aau_website_candidates_club_id (club_id),
    KEY idx_aau_website_candidates_domain (domain),
    KEY idx_aau_website_candidates_status (status),
    CONSTRAINT fk_aau_website_candidates_club
        FOREIGN KEY (club_id) REFERENCES aau_clubs (id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS aau_contact_artifacts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    club_id BIGINT UNSIGNED NOT NULL,
    submitted_by VARCHAR(128) NOT NULL,
    domain VARCHAR(255) NULL,
    status VARCHAR(64) NULL,
    artifact_path VARCHAR(512) NULL,
    artifact_url TEXT NULL,
    notes TEXT NULL,
    payload_json LONGTEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_aau_contact_artifacts_club_id (club_id),
    KEY idx_aau_contact_artifacts_domain (domain),
    CONSTRAINT fk_aau_contact_artifacts_club
        FOREIGN KEY (club_id) REFERENCES aau_clubs (id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS aau_webhook_events (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    club_id BIGINT UNSIGNED NULL,
    agent_id VARCHAR(128) NULL,
    event_type VARCHAR(64) NOT NULL,
    payload_json LONGTEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_aau_webhook_events_club_id (club_id),
    KEY idx_aau_webhook_events_agent_id (agent_id),
    KEY idx_aau_webhook_events_event_type (event_type),
    CONSTRAINT fk_aau_webhook_events_club
        FOREIGN KEY (club_id) REFERENCES aau_clubs (id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;