-- Aurelia Cloud — Snowflake-style DDL
-- NavAIgate Build Challenge 01: The Insight Engine
-- All data is fully synthetic. Aurelia Cloud is a fictional company.

CREATE OR REPLACE TABLE accounts (
    account_id       VARCHAR(16)   NOT NULL PRIMARY KEY COMMENT 'Unique account key, ACC-prefixed',
    name             VARCHAR(80)   COMMENT 'Customer company name (fictional)',
    industry         VARCHAR(40)   COMMENT 'Customer industry vertical',
    segment          VARCHAR(20)   COMMENT 'Enterprise / Mid-Market / SMB',
    region           VARCHAR(8)    COMMENT 'AMER / EMEA / APAC',
    country          VARCHAR(40)   COMMENT 'Customer HQ country',
    csm_owner        VARCHAR(40)   COMMENT 'Customer success manager (fictional)',
    arr_usd          NUMBER(12,0)  COMMENT 'Current annual recurring revenue, USD (sum of active subscriptions)',
    contract_start   DATE          COMMENT 'First contract start date',
    renewal_date     DATE          COMMENT 'Next contract renewal date',
    health_score     NUMBER(3,0)   COMMENT 'Composite account health, 0-100'
);

CREATE OR REPLACE TABLE contacts (
    contact_id       VARCHAR(16)   NOT NULL PRIMARY KEY COMMENT 'Unique contact key, CON-prefixed',
    account_id       VARCHAR(16)   COMMENT 'FK to accounts',
    full_name        VARCHAR(60)   COMMENT 'Contact name (fictional)',
    role             VARCHAR(40)   COMMENT 'Job role',
    email            VARCHAR(80)   COMMENT 'Contact email (@example.com)',
    is_champion      BOOLEAN       COMMENT 'TRUE when the contact champions Aurelia internally'
);

CREATE OR REPLACE TABLE products (
    product_id               VARCHAR(8)   NOT NULL PRIMARY KEY COMMENT 'Unique product key, PRD-prefixed',
    name                     VARCHAR(40)  COMMENT 'Product name',
    suite                    VARCHAR(16)  COMMENT 'Studio / Construct / Simulate',
    list_price_per_seat_usd  NUMBER(8,0)  COMMENT 'Annual list price per seat, USD',
    launch_date              DATE         COMMENT 'General-availability date'
);

CREATE OR REPLACE TABLE subscriptions (
    subscription_id  VARCHAR(16)   NOT NULL PRIMARY KEY COMMENT 'Unique subscription key, SUB-prefixed',
    account_id       VARCHAR(16)   COMMENT 'FK to accounts',
    product_id       VARCHAR(8)    COMMENT 'FK to products',
    seats            NUMBER(6,0)   COMMENT 'Licensed seats for the term',
    term_months      NUMBER(3,0)   COMMENT 'Contract term length: 12 / 24 / 36',
    start_date       DATE          COMMENT 'Term start',
    end_date         DATE          COMMENT 'Term end (a term is active when end_date is in the future)',
    arr_usd          NUMBER(12,0)  COMMENT 'Annualised contract value, USD (discounted)'
);

CREATE OR REPLACE TABLE usage_daily (
    date                DATE          COMMENT 'Activity date',
    account_id          VARCHAR(16)   COMMENT 'FK to accounts',
    product_id          VARCHAR(8)    COMMENT 'FK to products',
    active_users        NUMBER(8,0)   COMMENT 'Distinct users active that day on the product',
    sessions            NUMBER(10,0)  COMMENT 'Application sessions started',
    tokens_consumed     NUMBER(14,0)  COMMENT 'AI assistant tokens consumed',
    cloud_credits_used  NUMBER(12,1)  COMMENT 'Cloud compute credits burned (rendering / solving)',
    feature_events      NUMBER(12,0)  COMMENT 'Total instrumented feature events fired'
);

CREATE OR REPLACE TABLE support_tickets (
    ticket_id    VARCHAR(16)  NOT NULL PRIMARY KEY COMMENT 'Unique ticket key, TIC-prefixed',
    account_id   VARCHAR(16)  COMMENT 'FK to accounts',
    opened_date  DATE         COMMENT 'Date raised',
    closed_date  DATE         COMMENT 'Date closed (NULL while open)',
    severity     VARCHAR(12)  COMMENT 'Critical / High / Medium / Low',
    status       VARCHAR(12)  COMMENT 'Open / Resolved / Closed',
    category     VARCHAR(20)  COMMENT 'Ticket category',
    csat_score   NUMBER(1,0)  COMMENT 'Post-resolution satisfaction, 1-5 (NULL when unanswered)'
);

CREATE OR REPLACE TABLE opportunities (
    opp_id        VARCHAR(16)   NOT NULL PRIMARY KEY COMMENT 'Unique opportunity key, OPP-prefixed',
    account_id    VARCHAR(16)   COMMENT 'FK to accounts',
    type          VARCHAR(12)   COMMENT 'new / expansion / renewal',
    stage         VARCHAR(16)   COMMENT 'Discovery / Qualification / Proposal / Negotiation / Closed Won / Closed Lost',
    amount_usd    NUMBER(12,0)  COMMENT 'Opportunity value, USD',
    created_date  DATE          COMMENT 'Date the opportunity was opened',
    close_date    DATE          COMMENT 'Actual or expected close date',
    owner         VARCHAR(40)   COMMENT 'Account executive (fictional)',
    win_loss      VARCHAR(8)    COMMENT 'Won / Lost (NULL while open)'
);

CREATE OR REPLACE TABLE nps_responses (
    response_id       VARCHAR(16)  NOT NULL PRIMARY KEY COMMENT 'Unique response key, NPS-prefixed',
    account_id        VARCHAR(16)  COMMENT 'FK to accounts',
    contact_id        VARCHAR(16)  COMMENT 'FK to contacts',
    date              DATE         COMMENT 'Survey response date (quarterly waves)',
    score             NUMBER(2,0)  COMMENT 'NPS score, 0-10',
    verbatim_comment  VARCHAR(200) COMMENT 'Free-text comment'
);
