-- =====================================================================
-- ReviewsEmbed SaaS — Canonical Database Schema
-- Version: 1.0.0.0
-- Target: MySQL 8.0
-- Engine: InnoDB, Charset: utf8mb4, Collation: utf8mb4_unicode_ci
-- =====================================================================
-- Order is dependency-aware. Run top to bottom on a fresh database.
-- All FKs use ON DELETE CASCADE for tenant data so user removal is clean.
-- All monetary values are stored in cents (BIGINT) to avoid float drift.
-- All timestamps are stored as DATETIME UTC (no TIMESTAMP / TZ surprises).
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 1;

-- ---------------------------------------------------------------------
-- USERS — subscriber accounts
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    email               VARCHAR(255)    NOT NULL,
    password_hash       VARCHAR(255)    NOT NULL,
    is_admin            TINYINT(1)      NOT NULL DEFAULT 0,
    email_verified_at   DATETIME        NULL,
    last_login_at       DATETIME        NULL,
    created_at          DATETIME        NOT NULL,
    updated_at          DATETIME        NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- SESSIONS — server-side session tokens
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sessions (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id             BIGINT UNSIGNED NOT NULL,
    token               CHAR(64)        NOT NULL,
    ip_address          VARCHAR(45)     NULL,
    user_agent          VARCHAR(500)    NULL,
    expires_at          DATETIME        NOT NULL,
    created_at          DATETIME        NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_sessions_token (token),
    KEY ix_sessions_user (user_id),
    KEY ix_sessions_expires (expires_at),
    CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- PLANS — catalog of subscription tiers
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS plans (
    id                          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tier                        VARCHAR(32)     NOT NULL,
    display_name                VARCHAR(100)    NOT NULL,
    monthly_price_cents         BIGINT          NOT NULL,
    stripe_price_id             VARCHAR(255)    NULL,
    max_locations               INT             NOT NULL,
    sync_interval_minutes       INT             NOT NULL,
    max_widgets                 INT             NOT NULL,
    allows_custom_css           TINYINT(1)      NOT NULL DEFAULT 0,
    allows_ai_draft_reply       TINYINT(1)      NOT NULL DEFAULT 0,
    ai_draft_monthly_quota      INT             NOT NULL DEFAULT 0,
    allows_custom_font          TINYINT(1)      NOT NULL DEFAULT 0,
    allows_white_label          TINYINT(1)      NOT NULL DEFAULT 0,
    allows_api_access           TINYINT(1)      NOT NULL DEFAULT 0,
    is_active                   TINYINT(1)      NOT NULL DEFAULT 1,
    sort_order                  INT             NOT NULL DEFAULT 0,
    created_at                  DATETIME        NOT NULL,
    updated_at                  DATETIME        NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_plans_tier (tier)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- SUBSCRIPTIONS — Stripe subscription state mirror
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS subscriptions (
    id                          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id                     BIGINT UNSIGNED NOT NULL,
    plan_id                     BIGINT UNSIGNED NOT NULL,
    stripe_customer_id          VARCHAR(255)    NOT NULL,
    stripe_subscription_id      VARCHAR(255)    NULL,
    status                      VARCHAR(32)     NOT NULL DEFAULT 'incomplete',
    current_period_start        DATETIME        NULL,
    current_period_end          DATETIME        NULL,
    cancel_at_period_end        TINYINT(1)      NOT NULL DEFAULT 0,
    canceled_at                 DATETIME        NULL,
    created_at                  DATETIME        NOT NULL,
    updated_at                  DATETIME        NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_subscriptions_stripe_sub (stripe_subscription_id),
    KEY ix_subscriptions_user (user_id),
    KEY ix_subscriptions_status (status),
    CONSTRAINT fk_subscriptions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_subscriptions_plan FOREIGN KEY (plan_id) REFERENCES plans(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- GOOGLE_CONNECTIONS — OAuth tokens, encrypted at rest
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS google_connections (
    id                              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id                         BIGINT UNSIGNED NOT NULL,
    google_account_email            VARCHAR(255)    NOT NULL,
    encrypted_access_token          TEXT            NOT NULL,
    encrypted_refresh_token         TEXT            NOT NULL,
    access_token_expires_at         DATETIME        NOT NULL,
    scopes_granted                  TEXT            NOT NULL,
    last_synced_at                  DATETIME        NULL,
    sync_status                     VARCHAR(32)     NOT NULL DEFAULT 'ok',
    last_sync_error                 TEXT            NULL,
    created_at                      DATETIME        NOT NULL,
    updated_at                      DATETIME        NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_google_conn_user (user_id),
    KEY ix_google_conn_status (sync_status),
    CONSTRAINT fk_google_conn_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- BUSINESS_LOCATIONS — locations linked to a user's Google Business Profile
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS business_locations (
    id                          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id                     BIGINT UNSIGNED NOT NULL,
    google_account_id           VARCHAR(255)    NOT NULL,
    google_location_id          VARCHAR(255)    NOT NULL,
    google_resource_name        VARCHAR(500)    NOT NULL,
    display_name                VARCHAR(255)    NOT NULL,
    place_id                    VARCHAR(255)    NULL,
    address_line                VARCHAR(500)    NULL,
    average_rating              DECIMAL(3,2)    NULL,
    total_review_count          INT             NOT NULL DEFAULT 0,
    is_active                   TINYINT(1)      NOT NULL DEFAULT 1,
    last_synced_at              DATETIME        NULL,
    next_sync_due_at            DATETIME        NULL,
    created_at                  DATETIME        NOT NULL,
    updated_at                  DATETIME        NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_locations_user_gloc (user_id, google_location_id),
    KEY ix_locations_user (user_id),
    KEY ix_locations_next_sync (next_sync_due_at, is_active),
    CONSTRAINT fk_locations_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- REVIEWS — cached reviews from Google
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS reviews (
    id                          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    location_id                 BIGINT UNSIGNED NOT NULL,
    google_review_id            VARCHAR(255)    NOT NULL,
    google_review_name          VARCHAR(500)    NOT NULL,
    reviewer_display_name       VARCHAR(255)    NOT NULL,
    reviewer_photo_url          VARCHAR(1000)   NULL,
    reviewer_is_anonymous       TINYINT(1)      NOT NULL DEFAULT 0,
    star_rating                 TINYINT         NOT NULL,
    comment                     TEXT            NULL,
    create_time                 DATETIME        NOT NULL,
    update_time                 DATETIME        NOT NULL,
    has_owner_reply             TINYINT(1)      NOT NULL DEFAULT 0,
    owner_reply_text            TEXT            NULL,
    owner_reply_time            DATETIME        NULL,
    is_hidden                   TINYINT(1)      NOT NULL DEFAULT 0,
    fetched_at                  DATETIME        NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_reviews_google_id (google_review_id),
    KEY ix_reviews_location_time (location_id, create_time),
    KEY ix_reviews_rating (star_rating),
    CONSTRAINT fk_reviews_location FOREIGN KEY (location_id) REFERENCES business_locations(id) ON DELETE CASCADE,
    CONSTRAINT chk_reviews_rating CHECK (star_rating BETWEEN 1 AND 5)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- WIDGET_CONFIGS — per-widget customization (multiple widgets allowed on Pro+)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS widget_configs (
    id                          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id                     BIGINT UNSIGNED NOT NULL,
    public_widget_id            CHAR(32)        NOT NULL,
    widget_name                 VARCHAR(100)    NOT NULL DEFAULT 'My Reviews Widget',
    isolation_mode              VARCHAR(16)     NOT NULL DEFAULT 'iframe',

    -- Layout
    layout                      VARCHAR(16)     NOT NULL DEFAULT 'GRID',
    cards_per_row               TINYINT         NOT NULL DEFAULT 3,
    max_reviews_shown           INT             NOT NULL DEFAULT 12,
    pagination_style            VARCHAR(24)     NOT NULL DEFAULT 'none',
    width_mode                  VARCHAR(16)     NOT NULL DEFAULT 'container',
    max_width_px                INT             NOT NULL DEFAULT 1200,
    equal_height_cards          TINYINT(1)      NOT NULL DEFAULT 1,

    -- Theme + colors
    theme                       VARCHAR(16)     NOT NULL DEFAULT 'LIGHT',
    color_background            CHAR(7)         NOT NULL DEFAULT '#FFFFFF',
    color_card                  CHAR(7)         NOT NULL DEFAULT '#FFFFFF',
    color_text                  CHAR(7)         NOT NULL DEFAULT '#1A1A1A',
    color_accent                CHAR(7)         NOT NULL DEFAULT '#0EA5E9',
    color_star                  CHAR(7)         NOT NULL DEFAULT '#FBBF24',
    color_star_empty            CHAR(7)         NOT NULL DEFAULT '#E5E7EB',
    color_border                CHAR(7)         NOT NULL DEFAULT '#E5E7EB',
    color_hover_overlay         CHAR(7)         NOT NULL DEFAULT '#0EA5E9',

    -- Borders + shape
    border_style                VARCHAR(16)     NOT NULL DEFAULT 'solid',
    border_width_px             TINYINT         NOT NULL DEFAULT 1,
    border_radius_px            TINYINT         NOT NULL DEFAULT 8,
    card_shadow_intensity       TINYINT         NOT NULL DEFAULT 2,

    -- Typography
    font_family                 VARCHAR(100)    NOT NULL DEFAULT 'system',
    font_size                   VARCHAR(8)      NOT NULL DEFAULT 'medium',
    star_size                   VARCHAR(8)      NOT NULL DEFAULT 'medium',

    -- Content controls
    show_reviewer_photo         TINYINT(1)      NOT NULL DEFAULT 1,
    show_date                   TINYINT(1)      NOT NULL DEFAULT 1,
    show_owner_reply            TINYINT(1)      NOT NULL DEFAULT 1,
    show_verified_badge         TINYINT(1)      NOT NULL DEFAULT 1,
    truncate_at_chars           INT             NOT NULL DEFAULT 0,
    min_star_rating_filter      TINYINT         NOT NULL DEFAULT 1,
    hide_empty_text_reviews     TINYINT(1)      NOT NULL DEFAULT 0,

    -- Animation (entrance)
    animation_style             VARCHAR(16)     NOT NULL DEFAULT 'NONE',
    animation_duration_ms       INT             NOT NULL DEFAULT 400,
    animation_easing            VARCHAR(32)     NOT NULL DEFAULT 'ease-out',

    -- Carousel-specific
    autoplay_ms                 INT             NOT NULL DEFAULT 0,
    carousel_direction          VARCHAR(16)     NOT NULL DEFAULT 'ltr',
    carousel_pause_on_hover     TINYINT(1)      NOT NULL DEFAULT 1,
    carousel_transition         VARCHAR(16)     NOT NULL DEFAULT 'slide',

    -- Hover / mouse effects
    hover_effect                VARCHAR(16)     NOT NULL DEFAULT 'LIFT',
    star_hover_effect           VARCHAR(16)     NOT NULL DEFAULT 'NONE',
    cursor_style                VARCHAR(16)     NOT NULL DEFAULT 'default',
    click_behavior              VARCHAR(24)     NOT NULL DEFAULT 'none',
    click_url_template          VARCHAR(500)    NULL,

    -- Advanced (plan-gated)
    custom_css                  TEXT            NULL,
    hide_powered_by             TINYINT(1)      NOT NULL DEFAULT 0,
    locale                      VARCHAR(8)      NOT NULL DEFAULT 'en',

    is_active                   TINYINT(1)      NOT NULL DEFAULT 1,
    created_at                  DATETIME        NOT NULL,
    updated_at                  DATETIME        NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_widgets_public_id (public_widget_id),
    KEY ix_widgets_user (user_id),
    CONSTRAINT fk_widgets_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT chk_widgets_cards_per_row CHECK (cards_per_row BETWEEN 1 AND 6),
    CONSTRAINT chk_widgets_max_reviews CHECK (max_reviews_shown BETWEEN 1 AND 50),
    CONSTRAINT chk_widgets_min_rating CHECK (min_star_rating_filter BETWEEN 1 AND 5)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- WIDGET_LOCATIONS — which locations each widget pulls reviews from
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS widget_locations (
    id                          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    widget_config_id            BIGINT UNSIGNED NOT NULL,
    location_id                 BIGINT UNSIGNED NOT NULL,
    sort_order                  INT             NOT NULL DEFAULT 0,
    created_at                  DATETIME        NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_widget_locations (widget_config_id, location_id),
    KEY ix_widget_locations_loc (location_id),
    CONSTRAINT fk_wl_widget FOREIGN KEY (widget_config_id) REFERENCES widget_configs(id) ON DELETE CASCADE,
    CONSTRAINT fk_wl_location FOREIGN KEY (location_id) REFERENCES business_locations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- AI_REPLY_DRAFTS — Claude-drafted reply suggestions
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS ai_reply_drafts (
    id                          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id                     BIGINT UNSIGNED NOT NULL,
    review_id                   BIGINT UNSIGNED NOT NULL,
    tone                        VARCHAR(24)     NOT NULL DEFAULT 'professional',
    draft_text                  TEXT            NOT NULL,
    was_used                    TINYINT(1)      NOT NULL DEFAULT 0,
    created_at                  DATETIME        NOT NULL,
    PRIMARY KEY (id),
    KEY ix_drafts_user_month (user_id, created_at),
    KEY ix_drafts_review (review_id),
    CONSTRAINT fk_drafts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_drafts_review FOREIGN KEY (review_id) REFERENCES reviews(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- SYNC_JOBS — audit log of sync runs
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sync_jobs (
    id                          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    location_id                 BIGINT UNSIGNED NULL,
    user_id                     BIGINT UNSIGNED NULL,
    job_type                    VARCHAR(32)     NOT NULL,
    status                      VARCHAR(32)     NOT NULL DEFAULT 'running',
    reviews_added               INT             NOT NULL DEFAULT 0,
    reviews_updated             INT             NOT NULL DEFAULT 0,
    error_message               TEXT            NULL,
    started_at                  DATETIME        NOT NULL,
    finished_at                 DATETIME        NULL,
    PRIMARY KEY (id),
    KEY ix_sync_jobs_location (location_id, started_at),
    KEY ix_sync_jobs_status (status, started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- WEBHOOK_EVENTS — Stripe webhook idempotency log
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS webhook_events (
    id                          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    stripe_event_id             VARCHAR(255)    NOT NULL,
    event_type                  VARCHAR(100)    NOT NULL,
    processed_at                DATETIME        NOT NULL,
    processing_status           VARCHAR(32)     NOT NULL DEFAULT 'ok',
    error_message               TEXT            NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_webhook_stripe_id (stripe_event_id),
    KEY ix_webhook_type (event_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- AUDIT_LOG — security-relevant actions
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS audit_log (
    id                          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id                     BIGINT UNSIGNED NULL,
    action                      VARCHAR(64)     NOT NULL,
    target_type                 VARCHAR(64)     NULL,
    target_id                   VARCHAR(64)     NULL,
    ip_address                  VARCHAR(45)     NULL,
    user_agent                  VARCHAR(500)    NULL,
    metadata_json               TEXT            NULL,
    occurred_at                 DATETIME        NOT NULL,
    PRIMARY KEY (id),
    KEY ix_audit_user_time (user_id, occurred_at),
    KEY ix_audit_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- SYSTEM_CACHE — short-lived MySQL-backed cache
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS system_cache (
    id                          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    cache_key                   VARCHAR(255)    NOT NULL,
    cache_value                 LONGTEXT        NOT NULL,
    expires_at                  DATETIME        NOT NULL,
    created_at                  DATETIME        NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_cache_key (cache_key),
    KEY ix_cache_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- END OF SCHEMA
-- =====================================================================
