new-site/api/migrations/010_canada_crtc.sql

-- 010_canada_crtc.sql
-- Canadian CRTC Telecom Carrier Package orders.

BEGIN;

CREATE TABLE IF NOT EXISTS canada_crtc_orders (
    id                      SERIAL PRIMARY KEY,
    order_number            TEXT UNIQUE NOT NULL,
    -- Customer
    customer_name           TEXT NOT NULL,
    customer_email          TEXT NOT NULL,
    customer_phone          TEXT,
    customer_company        TEXT,
    -- Corporation details
    company_type            TEXT NOT NULL CHECK (company_type IN ('numbered', 'named')),
    company_name_choice1    TEXT,
    company_name_choice2    TEXT,
    company_name_choice3    TEXT,
    company_name_final      TEXT,
    bc_incorporation_number TEXT,
    -- Director
    director_name           TEXT NOT NULL,
    director_address        TEXT NOT NULL,
    director_citizenship    TEXT,
    -- Mailbox
    mailbox_unit_number     TEXT,
    mailbox_address         TEXT DEFAULT '329 Howe St, Vancouver, BC V6C 3N2',
    mailbox_account_email   TEXT,
    -- Telecom details
    services_description    TEXT NOT NULL,
    geographic_coverage     TEXT DEFAULT 'Canada-wide',
    include_bits            BOOLEAN DEFAULT TRUE,
    regulatory_contact_name TEXT,
    regulatory_contact_email TEXT,
    regulatory_contact_phone TEXT,
    -- ID upload
    id_upload_token         TEXT,
    id_verified             BOOLEAN DEFAULT FALSE,
    -- Pricing
    service_fee_cents       INTEGER DEFAULT 289900,
    government_fee_cents    INTEGER DEFAULT 38000,
    discount_code           TEXT,
    discount_cents          INTEGER DEFAULT 0,
    total_cents             INTEGER NOT NULL,
    -- Fulfillment status
    status                  TEXT DEFAULT 'received' CHECK (status IN (
        'received', 'mailbox_setup', 'name_reservation', 'incorporation',
        'crtc_letter', 'review', 'delivered', 'cancelled'
    )),
    automation_status       TEXT DEFAULT 'pending',
    automation_error        TEXT,
    -- Binder
    binder_generated        BOOLEAN DEFAULT FALSE,
    binder_minio_path       TEXT,
    binder_shipped          BOOLEAN DEFAULT FALSE,
    binder_tracking_number  TEXT,
    binder_shipped_at       TIMESTAMPTZ,
    -- Subscription
    subscription_id         TEXT,
    next_renewal_date       DATE,
    -- Timestamps
    admin_notes             TEXT,
    created_at              TIMESTAMPTZ DEFAULT now(),
    delivered_at            TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_crtc_orders_status ON canada_crtc_orders(status);
CREATE INDEX IF NOT EXISTS idx_crtc_orders_email ON canada_crtc_orders(customer_email);
CREATE INDEX IF NOT EXISTS idx_crtc_orders_number ON canada_crtc_orders(order_number);

COMMIT;