

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";      -- UUID generation
CREATE EXTENSION IF NOT EXISTS "pgcrypto";       -- Encryption functions
CREATE EXTENSION IF NOT EXISTS "btree_gist";     -- For exclusion constraints

-- =============================================================================
-- ENUM TYPES
-- =============================================================================

-- -----------------------------------------------------------------------------
-- Client & User Enums
-- -----------------------------------------------------------------------------

CREATE TYPE client_type_enum AS ENUM (
    'individual',
    'business',
    'trust',
    'nonprofit'
);

CREATE TYPE client_status_enum AS ENUM (
    'active',
    'inactive',
    'closed'
);

CREATE TYPE contact_method_enum AS ENUM (
    'email',
    'sms',
    'phone',
    'portal'
);

CREATE TYPE relationship_enum AS ENUM (
    'spouse',
    'dependent',
    'representative',
    'other'
);

-- Staff roles only. Clients do not have database user records.
-- Clients authenticate via tiered identity verification (INT-010 to INT-015)
-- and receive JWT tokens with client_id claims. See SECURITY_DESIGN.md Section 2.2.
CREATE TYPE user_role_enum AS ENUM (
    'admin',
    'preparer',
    'reviewer',
    'ea_cpa'
);

-- -----------------------------------------------------------------------------
-- Tax Return Enums
-- -----------------------------------------------------------------------------

CREATE TYPE return_type_enum AS ENUM (
    '1040',
    '1120',
    '1120S',
    '1065',
    '1041',
    '990',
    '706',
    '709'
);

CREATE TYPE filing_status_enum AS ENUM (
    'single',
    'married_joint',
    'married_separate',
    'head_of_household',
    'qualifying_widow'
);

CREATE TYPE priority_enum AS ENUM (
    'low',
    'normal',
    'high',
    'rush'
);

-- -----------------------------------------------------------------------------
-- Document Enums
-- -----------------------------------------------------------------------------

-- NOTE: Values match src/domain/document.py DocumentType enum exactly
CREATE TYPE document_type_enum AS ENUM (
    -- W-2 Forms
    'W-2',
    'W-2G',
    -- 1099 Forms
    '1099-INT',
    '1099-DIV',
    '1099-B',
    '1099-R',
    '1099-MISC',
    '1099-NEC',
    '1099-K',
    '1099-G',
    'SSA-1099',
    -- 1098 Forms
    '1098',
    '1098-E',
    '1098-T',
    -- K-1 Forms
    'K-1_1065',
    'K-1_1120S',
    'K-1_1041',
    -- Financial Statements
    'BANK_STATEMENT',
    'BROKERAGE_STATEMENT',
    -- Deduction Support
    'PROPERTY_TAX',
    'CHARITABLE_RECEIPT',
    'MEDICAL_EXPENSE',
    'BUSINESS_EXPENSE',
    'HOME_OFFICE',
    'VEHICLE_LOG',
    -- Identity Documents
    'ID_DOCUMENT',
    'DRIVERS_LICENSE',
    'PASSPORT',
    'SSN_CARD',
    -- Tax Returns and Documents
    'PRIOR_YEAR_RETURN',
    'DRAFT_RETURN',
    'FINAL_RETURN',
    'EFILE_CONFIRMATION',
    'ESTIMATED_PAYMENT',
    'ENGAGEMENT_LETTER',
    -- IRS Forms
    'FORM_8879',
    'FORM_7216',
    -- Other
    'OTHER',
    'unknown'
);

-- NOTE: Values match src/domain/document.py UploadSource enum exactly
CREATE TYPE document_source_enum AS ENUM (
    'portal',         -- Web portal upload
    'client_upload',  -- Client self-service upload
    'email',          -- Email attachment
    'smartvault',     -- SmartVault sync
    'mobile',         -- Mobile app upload
    'staff',          -- Staff upload on behalf of client
    'preparer'        -- Preparer upload
);

-- Upload source for document tracking (S4-001)
CREATE TYPE upload_source_enum AS ENUM (
    'portal',
    'email',
    'smartvault',
    'mobile',
    'staff'
);

-- Unmatched email status (S4-002)
CREATE TYPE unmatched_email_status_enum AS ENUM (
    'pending',
    'routed',
    'rejected'
);

-- Checklist item source (S4-007)
CREATE TYPE checklist_item_source_enum AS ENUM (
    'prior_year',
    'manual',
    'rule'
);

-- Checklist item status (S4-007)
CREATE TYPE checklist_item_status_enum AS ENUM (
    'expected',
    'received',
    'not_applicable',
    'waived'
);

-- NOTE: Values match src/domain/document.py DocumentStatus enum exactly
CREATE TYPE document_status_enum AS ENUM (
    'uploading',        -- Upload in progress
    'uploaded',         -- Upload complete, awaiting processing
    'scanning',         -- Malware scan in progress
    'quarantined',      -- Failed malware scan
    'classifying',      -- AI classification in progress
    'extracting',       -- OCR/extraction in progress
    'processing',       -- General processing in progress
    'review_required',  -- Low confidence, needs human review
    'processed',        -- Ready for use in tax prep
    'verified',         -- Verified by preparer
    'rejected',         -- Manually rejected
    'archived'          -- Post-filing archive
);

-- -----------------------------------------------------------------------------
-- Extraction Enums
-- -----------------------------------------------------------------------------

CREATE TYPE extraction_source_enum AS ENUM (
    'sureprep',
    'claude_bedrock',
    'manual'
);

CREATE TYPE verification_status_enum AS ENUM (
    'verified',
    'auto_verified',
    'pending_review',
    'partially_verified',
    'unverified',
    'error'
);

-- Extraction workflow status (S4-004)
CREATE TYPE extraction_status_enum AS ENUM (
    'pending',
    'processing',
    'completed',
    'review_required',
    'reviewed',
    'failed',
    'superseded'
);

CREATE TYPE field_source_enum AS ENUM (
    'text_layer',
    'ocr_high',
    'ocr_medium',
    'ocr_low',
    'manual'
);

-- -----------------------------------------------------------------------------
-- Workflow Enums
-- -----------------------------------------------------------------------------

CREATE TYPE workflow_state_enum AS ENUM (
    'intake',
    'documents_pending',
    'ready_for_prep',
    'ai_analysis',
    'in_prep',
    'pending_client_response',
    'ready_for_review',
    'in_review',
    'revisions_needed',
    'approved',
    'pending_signature',
    'ready_to_file',
    'filed',
    'accepted',
    'rejected',
    'complete',
    'needs_review'
);

-- -----------------------------------------------------------------------------
-- Communication Enums
-- -----------------------------------------------------------------------------

CREATE TYPE question_category_enum AS ENUM (
    'income_verification',
    'deduction_clarification',
    'life_event',
    'dependents',
    'business',
    'other'
);

CREATE TYPE question_source_enum AS ENUM (
    'ai_generated',
    'preparer_created'
);

CREATE TYPE question_status_enum AS ENUM (
    'pending',
    'answered',
    'expired',
    'withdrawn'
);

CREATE TYPE message_direction_enum AS ENUM (
    'inbound',
    'outbound'
);

CREATE TYPE communication_channel_enum AS ENUM (
    'portal',
    'email',
    'sms'
);

CREATE TYPE message_type_enum AS ENUM (
    'question_response',
    'document_submission',
    'status_update',
    'general'
);

CREATE TYPE note_type_enum AS ENUM (
    'general',
    'flag_for_review',
    'research_needed',
    'client_followup'
);

-- -----------------------------------------------------------------------------
-- Engagement & Compliance Enums
-- -----------------------------------------------------------------------------

CREATE TYPE engagement_type_enum AS ENUM (
    'tax_prep',
    'advisory',
    'representation'
);

CREATE TYPE engagement_status_enum AS ENUM (
    'draft',
    'sent',
    'viewed',
    'signed',
    'expired'
);

CREATE TYPE consent_status_enum AS ENUM (
    'pending',
    'signed',
    'expired',
    'revoked'
);

-- Consent type for Form 7216 (S3-003)
CREATE TYPE consent_type_enum AS ENUM (
    'disclosure_third_party',
    'use_other_services',
    'disclosure_spouse',
    'disclosure_representative'
);

CREATE TYPE actor_type_enum AS ENUM (
    'user',
    'system',
    'ai'
);

CREATE TYPE audit_action_enum AS ENUM (
    'create',
    'read',
    'update',
    'delete',
    'export',
    'print',
    'sign',
    'file'
);

CREATE TYPE audit_outcome_enum AS ENUM (
    'success',
    'failure',
    'denied'
);

-- -----------------------------------------------------------------------------
-- Financial Enums
-- -----------------------------------------------------------------------------

CREATE TYPE invoice_status_enum AS ENUM (
    'draft',
    'sent',
    'viewed',
    'partial',
    'paid',
    'overdue',
    'void'
);

CREATE TYPE payment_method_enum AS ENUM (
    'card',
    'ach',
    'check',
    'cash',
    'other'
);

CREATE TYPE payment_status_enum AS ENUM (
    'pending',
    'authorized',
    'paid',
    'failed',
    'refunded'
);

CREATE TYPE service_type_enum AS ENUM (
    'individual_1040',
    'business_1120',
    'business_1120s',
    'business_1065',
    'trust_1041',
    'nonprofit_990',
    'state_return',
    'extension',
    'amended_return',
    'quarterly_estimates',
    'tax_planning_session'
);

CREATE TYPE estimated_payment_status_enum AS ENUM (
    'scheduled',
    'reminder_sent',
    'confirmed_paid',
    'missed'
);

CREATE TYPE reminder_type_enum AS ENUM (
    '14_day',
    '3_day'
);

-- -----------------------------------------------------------------------------
-- E-Filing Enums
-- -----------------------------------------------------------------------------

CREATE TYPE transmission_type_enum AS ENUM (
    'original',
    'resubmission',
    'amended'
);

CREATE TYPE transmission_status_enum AS ENUM (
    'queued',
    'transmitting',
    'transmitted',
    'error'
);

CREATE TYPE ack_type_enum AS ENUM (
    'accepted',
    'rejected',
    'pending'
);

CREATE TYPE rejection_category_enum AS ENUM (
    'data_mismatch',
    'missing_info',
    'duplicate',
    'technical',
    'identity'  -- IRS standard for IP PIN verification requirements
);

CREATE TYPE resolution_status_enum AS ENUM (
    'pending',
    'in_progress',
    'resolved',
    'escalated'
);

CREATE TYPE extension_status_enum AS ENUM (
    'draft',
    'filed',
    'accepted',
    'rejected'
);

-- -----------------------------------------------------------------------------
-- Identity Verification Enums
-- -----------------------------------------------------------------------------

CREATE TYPE verification_tier_enum AS ENUM (
    'tier1',
    'tier2',
    'tier3'
);

CREATE TYPE id_verification_status_enum AS ENUM (
    'initiated',
    'in_progress',
    'passed',
    'failed',
    'expired'
);

CREATE TYPE verification_method_enum AS ENUM (
    'prior_year_agi',
    'magic_link',
    'id_document',
    'video_call',
    'in_person',
    'notarized'
);

CREATE TYPE meet_session_status_enum AS ENUM (
    'scheduled',
    'in_progress',
    'completed',
    'no_show',
    'cancelled'
);

CREATE TYPE meet_outcome_enum AS ENUM (
    'verified',
    'not_verified'
);

CREATE TYPE magic_link_status_enum AS ENUM (
    'pending',
    'used',
    'expired',
    'revoked'
);

CREATE TYPE email_verification_status_enum AS ENUM (
    'pending',
    'verified',
    'expired',
    'invalidated'
);

-- -----------------------------------------------------------------------------
-- Integration Enums
-- -----------------------------------------------------------------------------

CREATE TYPE sync_status_enum AS ENUM (
    'synced',
    'pending',
    'error'
);

CREATE TYPE folder_type_enum AS ENUM (
    'source_docs',
    'final_returns',
    'engagement',
    'other'
);

CREATE TYPE binder_status_enum AS ENUM (
    'created',
    'uploading',
    'processing',
    'ready',
    'exported',
    'error'
);

CREATE TYPE sureprep_extraction_status_enum AS ENUM (
    'verified',
    'auto_verified',
    'pending_review',
    'unverified',
    'error'
);

CREATE TYPE signature_doc_type_enum AS ENUM (
    'form_8879',
    'engagement',
    'consent',
    'other'
);

CREATE TYPE signature_status_enum AS ENUM (
    'pending',
    'sent',
    'viewed',
    'signed',
    'declined',
    'expired'
);

CREATE TYPE stripe_status_enum AS ENUM (
    'requires_payment_method',
    'requires_action',
    'processing',
    'succeeded',
    'canceled'
);


-- =============================================================================
-- UTILITY FUNCTIONS
-- =============================================================================

-- Function to update updated_at timestamp automatically
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


-- =============================================================================
-- CORE DOMAIN TABLES
-- =============================================================================

-- -----------------------------------------------------------------------------
-- ACCOUNT_NUMBER_SEQ - Sequential counter for generating account numbers
-- MTG-D04: Uses 'global' key for A##### format (configurable prefix)
-- Legacy format YYYYMMDD-NNNN still supported for existing clients
-- -----------------------------------------------------------------------------

CREATE TABLE account_number_seq (
    date_prefix             VARCHAR(10) PRIMARY KEY,  -- 'global' or YYYYMMDD format
    next_val                INTEGER NOT NULL DEFAULT 1,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Initialize global sequence for new A##### format (MTG-D04)
-- Set to 10003 because UPSERT returns post-increment value
-- Seed data uses A10001, A10002, A10003, so first new client gets A10004
INSERT INTO account_number_seq (date_prefix, next_val) VALUES ('global', 10003);

COMMENT ON TABLE account_number_seq IS 'Sequential counter for account numbers. Uses "global" for A##### format (MTG-D04)';

-- -----------------------------------------------------------------------------
-- CLIENT - Master client records
-- -----------------------------------------------------------------------------

CREATE TABLE client (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    account_number          VARCHAR(20) UNIQUE NOT NULL,
    client_type             client_type_enum NOT NULL,
    name                    VARCHAR(255) NOT NULL,
    email                   VARCHAR(255) NOT NULL,
    phone                   VARCHAR(20),
    address_line1           VARCHAR(255),
    address_line2           VARCHAR(255),
    city                    VARCHAR(100),
    state                   VARCHAR(2),
    zip                     VARCHAR(10),
    -- Sensitive fields - application layer handles encryption/decryption
    ssn_last4               VARCHAR(4),
    ein                     VARCHAR(10),
    -- MTG-002: Department/Firm tracking for multi-firm practices
    department              VARCHAR(100),
    -- MTG-004: Business client flag and details
    is_business             BOOLEAN DEFAULT FALSE,
    business_details        JSONB,  -- {ein, business_type, fiscal_year_end, state_of_incorporation}
    date_of_birth           DATE,
    status                  client_status_enum NOT NULL DEFAULT 'active',
    prior_year_agi          DECIMAL(12,2),
    -- COMP-006: Field-level encryption columns
    -- Encrypted BYTEA columns store AES-256-GCM ciphertext
    -- Hash columns store HMAC-SHA256 blind indexes for searching
    ssn_last4_encrypted     BYTEA,
    ssn_last4_hash          VARCHAR(64),
    ein_encrypted           BYTEA,
    date_of_birth_encrypted BYTEA,
    date_of_birth_hash      VARCHAR(64),
    prior_year_agi_encrypted BYTEA,
    duplicate_check_hash    VARCHAR(64),
    -- Portal and verification tracking
    email_verified_at       TIMESTAMP,
    terms_accepted_at       TIMESTAMP,
    terms_version           VARCHAR(20),
    portal_activated_at     TIMESTAMP,
    preferred_contact_method contact_method_enum DEFAULT 'email',
    deleted_at              TIMESTAMP,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_client_email ON client(email);
CREATE INDEX idx_client_state_status ON client(state, status);
CREATE INDEX idx_client_status ON client(status) WHERE deleted_at IS NULL;
-- MTG-002: Department index for efficient filtering
CREATE INDEX idx_client_department ON client(department) WHERE department IS NOT NULL;
-- MTG-004: Business client index
CREATE INDEX idx_client_is_business ON client(is_business) WHERE is_business = TRUE;
-- COMP-006: Duplicate detection index on blind index hash
CREATE INDEX idx_client_duplicate_check_hash ON client(duplicate_check_hash)
    WHERE duplicate_check_hash IS NOT NULL;

CREATE TRIGGER update_client_updated_at
    BEFORE UPDATE ON client
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE client IS 'Master record for all clients (individuals, businesses, trusts)';
COMMENT ON COLUMN client.ssn_last4 IS 'Last 4 of SSN for Tier 1 verification - stored via application-level encryption';
COMMENT ON COLUMN client.prior_year_agi IS 'Prior year AGI for Tier 1 identity verification';

-- -----------------------------------------------------------------------------
-- CLIENT_CONTACT - Additional contacts per client
-- -----------------------------------------------------------------------------

CREATE TABLE client_contact (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    client_id               UUID NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    relationship            relationship_enum NOT NULL,
    first_name              VARCHAR(100) NOT NULL,
    last_name               VARCHAR(100) NOT NULL,
    email                   VARCHAR(255),
    phone                   VARCHAR(20),
    ssn_last4               VARCHAR(4),
    date_of_birth           DATE,
    -- COMP-006: Field-level encryption columns
    ssn_last4_encrypted     BYTEA,
    date_of_birth_encrypted BYTEA,
    is_primary              BOOLEAN DEFAULT FALSE,
    can_receive_documents   BOOLEAN DEFAULT FALSE,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_client_contact_client_id ON client_contact(client_id);
CREATE INDEX idx_client_contact_relationship ON client_contact(client_id, relationship);

CREATE TRIGGER update_client_contact_updated_at
    BEFORE UPDATE ON client_contact
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE client_contact IS 'Additional contacts associated with a client (spouse, dependents, representatives)';

-- -----------------------------------------------------------------------------
-- USERS - Staff user accounts (preparers, reviewers, admins)
-- Note: Named "users" to avoid PostgreSQL reserved word "user"
-- -----------------------------------------------------------------------------

CREATE TABLE users (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email                   VARCHAR(255) UNIQUE NOT NULL,
    first_name              VARCHAR(100) NOT NULL,
    last_name               VARCHAR(100) NOT NULL,
    role                    user_role_enum NOT NULL,
    ptin                    VARCHAR(10),
    -- COMP-006: Field-level encryption column
    ptin_encrypted          BYTEA,
    is_active               BOOLEAN DEFAULT TRUE,
    mfa_enabled             BOOLEAN DEFAULT FALSE,
    last_login_at           TIMESTAMP,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW(),

    -- EA/CPA must have valid PTIN
    CONSTRAINT chk_ea_cpa_ptin CHECK (
        role != 'ea_cpa' OR ptin IS NOT NULL
    )
);

CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_is_active ON users(is_active);

CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE users IS 'Staff user accounts (preparers, reviewers, admins)';
COMMENT ON COLUMN users.ptin IS 'Preparer Tax Identification Number (format: Pxxxxxxxx)';
COMMENT ON CONSTRAINT chk_ea_cpa_ptin ON users IS 'EA/CPA role requires valid PTIN per Circular 230';

-- -----------------------------------------------------------------------------
-- TAX_RETURN - Individual tax return records
-- -----------------------------------------------------------------------------

CREATE TABLE tax_return (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    client_id               UUID NOT NULL REFERENCES client(id) ON DELETE RESTRICT,
    tax_year                INTEGER NOT NULL,
    return_type             return_type_enum NOT NULL,
    filing_status           filing_status_enum,
    assigned_preparer_id    UUID REFERENCES users(id) ON DELETE SET NULL,
    assigned_reviewer_id    UUID REFERENCES users(id) ON DELETE SET NULL,
    completion_percentage   INTEGER DEFAULT 0 CHECK (completion_percentage >= 0 AND completion_percentage <= 100),
    priority                priority_enum DEFAULT 'normal',
    due_date                DATE,
    extended_due_date       DATE,
    filed_date              TIMESTAMP,
    accepted_date           TIMESTAMP,
    federal_refund_amount   DECIMAL(12,2),
    state_refund_amount     DECIMAL(12,2),
    deleted_at              TIMESTAMP,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW(),

    -- One return per client per year per type
    CONSTRAINT uq_client_year_type UNIQUE (client_id, tax_year, return_type)
);

CREATE INDEX idx_tax_return_client_id ON tax_return(client_id);
CREATE INDEX idx_tax_return_year_type ON tax_return(tax_year, return_type);
CREATE INDEX idx_tax_return_preparer ON tax_return(assigned_preparer_id);
CREATE INDEX idx_tax_return_reviewer ON tax_return(assigned_reviewer_id);
CREATE INDEX idx_tax_return_priority ON tax_return(priority) WHERE deleted_at IS NULL;

CREATE TRIGGER update_tax_return_updated_at
    BEFORE UPDATE ON tax_return
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE tax_return IS 'Individual tax return record for a specific client and tax year';
COMMENT ON COLUMN tax_return.federal_refund_amount IS 'Federal refund amount (negative = amount owed)';
COMMENT ON COLUMN tax_return.completion_percentage IS 'Progress indicator derived from workflow state';

-- -----------------------------------------------------------------------------
-- DOCUMENT - Metadata for uploaded tax documents
-- -----------------------------------------------------------------------------

CREATE TABLE document (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    client_id               UUID NOT NULL REFERENCES client(id) ON DELETE RESTRICT,
    tax_return_id           UUID REFERENCES tax_return(id) ON DELETE SET NULL,
    document_type           document_type_enum NOT NULL,
    original_filename       VARCHAR(255) NOT NULL,
    s3_key                  VARCHAR(500) UNIQUE NOT NULL,
    s3_bucket               VARCHAR(100) NOT NULL,
    file_size_bytes         BIGINT NOT NULL,
    mime_type               VARCHAR(100) NOT NULL,
    file_hash               VARCHAR(64) NOT NULL,
    source                  document_source_enum NOT NULL,
    is_native_pdf           BOOLEAN,
    malware_scanned         BOOLEAN DEFAULT FALSE,
    malware_scan_date       TIMESTAMP,
    malware_clean           BOOLEAN,
    status                  document_status_enum DEFAULT 'uploaded',
    tax_year                INTEGER,
    uploaded_by             UUID REFERENCES users(id) ON DELETE SET NULL,
    upload_batch_id         UUID,  -- Links to original pending_document batch after assignment
    -- MTG-006: Status override fields
    status_override         BOOLEAN DEFAULT FALSE,
    status_override_by      UUID REFERENCES users(id),
    status_override_reason  VARCHAR(500),
    status_override_at      TIMESTAMPTZ,
    status_override_from    VARCHAR(50),
    deleted_at              TIMESTAMP,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_document_client_id ON document(client_id);
CREATE INDEX idx_document_upload_batch ON document(upload_batch_id) WHERE upload_batch_id IS NOT NULL;
CREATE INDEX idx_document_tax_return_id ON document(tax_return_id);
CREATE INDEX idx_document_client_year ON document(client_id, tax_year);
CREATE INDEX idx_document_type ON document(document_type);
CREATE INDEX idx_document_status ON document(status) WHERE deleted_at IS NULL;
-- MTG-006: Index for finding overridden documents
CREATE INDEX idx_document_status_override ON document(status_override) WHERE status_override = TRUE;

CREATE TRIGGER update_document_updated_at
    BEFORE UPDATE ON document
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE document IS 'Metadata for uploaded tax documents (actual content stored in S3)';
COMMENT ON COLUMN document.file_hash IS 'SHA-256 hash for integrity verification';
COMMENT ON COLUMN document.is_native_pdf IS 'True if native PDF with text layer (not scanned)';

-- -----------------------------------------------------------------------------
-- DOCUMENT_UPLOAD - Multipart upload progress tracking (S4-001)
-- -----------------------------------------------------------------------------

CREATE TABLE document_upload (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    client_id               UUID NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    tax_year                INTEGER NOT NULL,
    original_filename       VARCHAR(255) NOT NULL,
    content_type            VARCHAR(100) NOT NULL,
    file_size_bytes         BIGINT NOT NULL,
    upload_source           upload_source_enum NOT NULL DEFAULT 'portal',
    s3_upload_id            VARCHAR(500) NOT NULL,
    parts_uploaded          INTEGER NOT NULL DEFAULT 0,
    total_parts             INTEGER NOT NULL DEFAULT 0,
    bytes_uploaded          BIGINT NOT NULL DEFAULT 0,
    status                  VARCHAR(50) NOT NULL DEFAULT 'in_progress',
    document_id             UUID REFERENCES document(id),
    started_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    completed_at            TIMESTAMP,
    expires_at              TIMESTAMP
);

CREATE INDEX idx_document_upload_client ON document_upload(client_id);
CREATE INDEX idx_document_upload_status ON document_upload(status) WHERE status = 'in_progress';

COMMENT ON TABLE document_upload IS 'Multipart upload progress tracking for large files';
COMMENT ON COLUMN document_upload.s3_upload_id IS 'S3 multipart upload ID';

-- -----------------------------------------------------------------------------
-- DOCUMENT_EXTRACTION - Extracted structured data from documents
-- -----------------------------------------------------------------------------

CREATE TABLE document_extraction (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    document_id             UUID NOT NULL REFERENCES document(id) ON DELETE CASCADE,
    tax_return_id           UUID REFERENCES tax_return(id) ON DELETE SET NULL,
    extraction_source       extraction_source_enum NOT NULL,
    sureprep_binder_id      UUID,  -- FK added after sureprep_binder table created
    overall_confidence      DECIMAL(3,2) CHECK (overall_confidence >= 0 AND overall_confidence <= 1),
    verification_status     verification_status_enum NOT NULL,
    verified_by             UUID REFERENCES users(id) ON DELETE SET NULL,
    verified_at             TIMESTAMP,
    extracted_data          JSONB,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_document_extraction_document_id ON document_extraction(document_id);
CREATE INDEX idx_document_extraction_tax_return_id ON document_extraction(tax_return_id);
CREATE INDEX idx_document_extraction_status ON document_extraction(verification_status);

CREATE TRIGGER update_document_extraction_updated_at
    BEFORE UPDATE ON document_extraction
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE document_extraction IS 'Extracted structured data from a document';
COMMENT ON COLUMN document_extraction.overall_confidence IS 'Overall extraction confidence (0.00-1.00)';
COMMENT ON COLUMN document_extraction.extracted_data IS 'Full extracted data as JSON';

-- -----------------------------------------------------------------------------
-- EXTRACTED_FIELD - Individual fields from extraction
-- -----------------------------------------------------------------------------

CREATE TABLE extracted_field (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    extraction_id           UUID NOT NULL REFERENCES document_extraction(id) ON DELETE CASCADE,
    field_name              VARCHAR(100) NOT NULL,
    field_value             TEXT,
    form_box                VARCHAR(20),
    confidence              DECIMAL(3,2) CHECK (confidence >= 0 AND confidence <= 1),
    source                  field_source_enum NOT NULL,
    is_verified             BOOLEAN DEFAULT FALSE,
    original_value          TEXT,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_extracted_field_extraction_id ON extracted_field(extraction_id);
CREATE INDEX idx_extracted_field_name ON extracted_field(extraction_id, field_name);

COMMENT ON TABLE extracted_field IS 'Individual field extracted from a document';
COMMENT ON COLUMN extracted_field.form_box IS 'Tax form box reference (e.g., Box 1, Box 2a)';
COMMENT ON COLUMN extracted_field.original_value IS 'Value before manual correction';

-- -----------------------------------------------------------------------------
-- EXTRACTION - Document extraction results (S4-004)
-- This is the main extraction table used by extraction workflows
-- -----------------------------------------------------------------------------

CREATE TABLE extraction (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    document_id             UUID NOT NULL REFERENCES document(id) ON DELETE CASCADE,
    extraction_source       extraction_source_enum NOT NULL DEFAULT 'claude_bedrock',
    status                  extraction_status_enum NOT NULL DEFAULT 'pending',
    raw_extraction          JSONB DEFAULT '{}'::jsonb,
    validated_extraction    JSONB,
    overall_confidence      DECIMAL(5,4) DEFAULT 0.0,
    fields                  JSONB DEFAULT '[]'::jsonb,
    fields_requiring_review JSONB DEFAULT '[]'::jsonb,
    reviewed_by             UUID REFERENCES users(id) ON DELETE SET NULL,
    reviewed_at             TIMESTAMP,
    notes                   TEXT,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_extraction_document_id ON extraction(document_id);
CREATE INDEX idx_extraction_status ON extraction(status);
CREATE INDEX idx_extraction_review ON extraction(status) WHERE status = 'review_required';

CREATE TRIGGER update_extraction_updated_at
    BEFORE UPDATE ON extraction
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE extraction IS 'Document extraction results from AI/SurePrep processing';
COMMENT ON COLUMN extraction.raw_extraction IS 'Raw extraction data before validation';
COMMENT ON COLUMN extraction.validated_extraction IS 'Validated extraction data after review';

-- -----------------------------------------------------------------------------
-- EXTRACTION_CORRECTION - Audit trail for extraction corrections (S4-008)
-- -----------------------------------------------------------------------------

CREATE TABLE extraction_correction (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    extraction_id           UUID NOT NULL REFERENCES extraction(id) ON DELETE CASCADE,
    document_id             UUID NOT NULL REFERENCES document(id) ON DELETE CASCADE,
    field_name              VARCHAR(100) NOT NULL,
    original_value          TEXT,
    corrected_value         TEXT,
    original_confidence     DECIMAL(5,4),
    reason                  TEXT,
    corrected_by            UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_extraction_correction_extraction ON extraction_correction(extraction_id);
CREATE INDEX idx_extraction_correction_document ON extraction_correction(document_id);
CREATE INDEX idx_extraction_correction_field ON extraction_correction(field_name);
CREATE INDEX idx_extraction_correction_created ON extraction_correction(created_at);

COMMENT ON TABLE extraction_correction IS 'Audit trail of corrections made to extractions';
COMMENT ON COLUMN extraction_correction.original_confidence IS 'AI confidence for original value';

-- -----------------------------------------------------------------------------
-- CHECKLIST_ITEM - Expected document tracking (S4-007)
-- -----------------------------------------------------------------------------

CREATE TABLE checklist_item (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    client_id               UUID NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    tax_year                INTEGER NOT NULL,
    document_type           document_type_enum NOT NULL,
    description             VARCHAR(500) NOT NULL,
    is_required             BOOLEAN NOT NULL DEFAULT TRUE,
    source                  checklist_item_source_enum NOT NULL DEFAULT 'manual',
    status                  checklist_item_status_enum NOT NULL DEFAULT 'expected',
    document_id             UUID REFERENCES document(id) ON DELETE SET NULL,
    received_at             TIMESTAMP,
    reminder_sent_at        TIMESTAMP,
    notes                   TEXT,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_checklist_item_client ON checklist_item(client_id, tax_year);
CREATE INDEX idx_checklist_item_status ON checklist_item(status);
CREATE INDEX idx_checklist_item_document ON checklist_item(document_id);
CREATE INDEX idx_checklist_item_pending ON checklist_item(client_id, status) WHERE status = 'expected';

CREATE TRIGGER update_checklist_item_updated_at
    BEFORE UPDATE ON checklist_item
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE checklist_item IS 'Expected documents checklist per client/year';
COMMENT ON COLUMN checklist_item.source IS 'How this checklist item was created';

-- -----------------------------------------------------------------------------
-- UNMATCHED_EMAIL - Email routing queue (S4-002)
-- -----------------------------------------------------------------------------

CREATE TABLE unmatched_email (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    message_id              VARCHAR(255) NOT NULL,
    from_address            VARCHAR(255) NOT NULL,
    from_name               VARCHAR(255),
    subject                 VARCHAR(500),
    received_at             TIMESTAMP NOT NULL DEFAULT NOW(),
    attachment_count        INTEGER NOT NULL DEFAULT 0,
    raw_email_s3_key        VARCHAR(500),
    status                  unmatched_email_status_enum NOT NULL DEFAULT 'pending',
    routed_to_client_id     UUID REFERENCES client(id) ON DELETE SET NULL,
    routed_by               UUID REFERENCES users(id) ON DELETE SET NULL,
    routed_at               TIMESTAMP,
    notes                   TEXT,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_unmatched_email_status ON unmatched_email(status);
CREATE INDEX idx_unmatched_email_from ON unmatched_email(from_address);
CREATE INDEX idx_unmatched_email_created ON unmatched_email(created_at);
CREATE INDEX idx_unmatched_email_pending ON unmatched_email(status) WHERE status = 'pending';

COMMENT ON TABLE unmatched_email IS 'Queue of emails that need manual routing to clients';
COMMENT ON COLUMN unmatched_email.message_id IS 'Email Message-ID header';

-- -----------------------------------------------------------------------------
-- PENDING_DOCUMENT - Staging table for unassigned document uploads
-- Used in "Create Client from Documents" flow per metadata architecture plan
-- -----------------------------------------------------------------------------

CREATE TABLE pending_document (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    upload_batch_id         UUID NOT NULL,
    tax_year                INTEGER NOT NULL,
    original_filename       VARCHAR(255) NOT NULL,
    s3_key                  VARCHAR(500) UNIQUE NOT NULL,
    file_size_bytes         BIGINT NOT NULL,
    mime_type               VARCHAR(100) NOT NULL,
    file_hash               VARCHAR(64) NOT NULL,
    document_type           document_type_enum,
    document_type_confidence DECIMAL(3,2) CHECK (document_type_confidence >= 0 AND document_type_confidence <= 1),
    extracted_metadata      JSONB,
    client_identifying      JSONB,
    overall_confidence      DECIMAL(3,2) CHECK (overall_confidence >= 0 AND overall_confidence <= 1),
    uploaded_by             UUID REFERENCES users(id) ON DELETE SET NULL,
    upload_source           document_source_enum DEFAULT 'staff',
    created_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_pending_document_batch ON pending_document(upload_batch_id);
CREATE INDEX idx_pending_document_hash ON pending_document(file_hash);

COMMENT ON TABLE pending_document IS 'Staging table for documents uploaded before client assignment';
COMMENT ON COLUMN pending_document.upload_batch_id IS 'Groups documents uploaded together for new client creation';
COMMENT ON COLUMN pending_document.client_identifying IS 'Extracted fields for client pre-population (name, SSN-4, address)';

-- -----------------------------------------------------------------------------
-- DOCUMENT_METADATA - Cached metadata for assigned documents (OPT-001)
-- Reduces AI token usage by caching extraction results
-- -----------------------------------------------------------------------------

CREATE TABLE document_metadata (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    -- document_id is UNIQUE because each document has exactly one metadata record
    -- Required for ON CONFLICT upsert in repository
    document_id             UUID NOT NULL UNIQUE REFERENCES document(id) ON DELETE CASCADE,
    metadata_version        VARCHAR(10) DEFAULT '1.0',
    document_type           VARCHAR(50),
    document_type_confidence DECIMAL(3,2) CHECK (document_type_confidence >= 0 AND document_type_confidence <= 1),
    client_identifying      JSONB,
    extracted_fields        JSONB,
    overall_confidence      DECIMAL(3,2) CHECK (overall_confidence >= 0 AND overall_confidence <= 1),
    extraction_source       VARCHAR(20),
    s3_metadata_key         VARCHAR(500),
    upload_batch_id         UUID,
    is_stale                BOOLEAN DEFAULT FALSE,
    stale_reason            TEXT,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_document_metadata_document_id ON document_metadata(document_id);
CREATE INDEX idx_document_metadata_batch ON document_metadata(upload_batch_id);
CREATE INDEX idx_document_metadata_stale ON document_metadata(is_stale) WHERE is_stale = TRUE;

CREATE TRIGGER update_document_metadata_updated_at
    BEFORE UPDATE ON document_metadata
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE document_metadata IS 'Cached document metadata for AI cost optimization (OPT-001)';
COMMENT ON COLUMN document_metadata.is_stale IS 'True if metadata needs re-extraction (document updated)';
COMMENT ON COLUMN document_metadata.s3_metadata_key IS 'S3 key for markdown/JSON metadata files';


-- =============================================================================
-- WORKFLOW TABLES
-- =============================================================================

-- -----------------------------------------------------------------------------
-- WORKFLOW_STATE - Current state per return
-- -----------------------------------------------------------------------------

CREATE TABLE workflow_state (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tax_return_id           UUID UNIQUE NOT NULL REFERENCES tax_return(id) ON DELETE CASCADE,
    current_state           workflow_state_enum NOT NULL,
    entered_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    assigned_to             UUID REFERENCES users(id) ON DELETE SET NULL,
    progress_percentage     INTEGER DEFAULT 0,
    notes                   TEXT,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_workflow_state_current ON workflow_state(current_state);
CREATE INDEX idx_workflow_state_assigned ON workflow_state(assigned_to);

CREATE TRIGGER update_workflow_state_updated_at
    BEFORE UPDATE ON workflow_state
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE workflow_state IS 'Current workflow state for a tax return (one per return)';

-- -----------------------------------------------------------------------------
-- WORKFLOW_HISTORY - Audit trail of state transitions
-- Partitioned by month for 7-year retention
-- -----------------------------------------------------------------------------

CREATE TABLE workflow_history (
    id                      UUID NOT NULL DEFAULT uuid_generate_v4(),
    tax_return_id           UUID NOT NULL REFERENCES tax_return(id) ON DELETE CASCADE,
    from_state              workflow_state_enum,
    to_state                workflow_state_enum NOT NULL,
    transitioned_at         TIMESTAMP NOT NULL DEFAULT NOW(),
    transitioned_by         UUID REFERENCES users(id) ON DELETE SET NULL,
    transition_reason       TEXT,
    duration_seconds        INTEGER,
    metadata                JSONB,
    PRIMARY KEY (id, transitioned_at)
) PARTITION BY RANGE (transitioned_at);

CREATE INDEX idx_workflow_history_return ON workflow_history(tax_return_id);
CREATE INDEX idx_workflow_history_date ON workflow_history(transitioned_at);
CREATE INDEX idx_workflow_history_to_state ON workflow_history(to_state);

COMMENT ON TABLE workflow_history IS 'Audit trail of all workflow state transitions';
COMMENT ON COLUMN workflow_history.from_state IS 'Previous state (null for initial state)';
COMMENT ON COLUMN workflow_history.duration_seconds IS 'Time spent in from_state';

-- Create partitions for current and next 2 years (add more as needed)
CREATE TABLE workflow_history_2024 PARTITION OF workflow_history
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE workflow_history_2025 PARTITION OF workflow_history
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE workflow_history_2026 PARTITION OF workflow_history
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
CREATE TABLE workflow_history_2027 PARTITION OF workflow_history
    FOR VALUES FROM ('2027-01-01') TO ('2028-01-01');


-- =============================================================================
-- COMMUNICATION TABLES
-- =============================================================================

-- -----------------------------------------------------------------------------
-- CLIENT_QUESTION - Questions generated for clients
-- -----------------------------------------------------------------------------

CREATE TABLE client_question (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tax_return_id           UUID NOT NULL REFERENCES tax_return(id) ON DELETE CASCADE,
    client_id               UUID NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    question_text           TEXT NOT NULL,
    question_category       question_category_enum NOT NULL,
    source                  question_source_enum NOT NULL,
    priority                priority_enum DEFAULT 'normal',
    status                  question_status_enum DEFAULT 'pending',
    asked_by                UUID REFERENCES users(id) ON DELETE SET NULL,
    asked_at                TIMESTAMP NOT NULL DEFAULT NOW(),
    due_date                DATE,
    response_text           TEXT,
    responded_at            TIMESTAMP,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_client_question_tax_return ON client_question(tax_return_id);
CREATE INDEX idx_client_question_client ON client_question(client_id);
CREATE INDEX idx_client_question_status ON client_question(status);
CREATE INDEX idx_client_question_escalation ON client_question(status, due_date);

CREATE TRIGGER update_client_question_updated_at
    BEFORE UPDATE ON client_question
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE client_question IS 'Questions generated for clients during preparation';

-- -----------------------------------------------------------------------------
-- CLIENT_MESSAGE - Messages exchanged with clients
-- -----------------------------------------------------------------------------

CREATE TABLE client_message (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    client_id               UUID NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    tax_return_id           UUID REFERENCES tax_return(id) ON DELETE SET NULL,
    question_id             UUID REFERENCES client_question(id) ON DELETE SET NULL,
    direction               message_direction_enum NOT NULL,
    channel                 communication_channel_enum NOT NULL,
    message_type            message_type_enum NOT NULL,
    subject                 VARCHAR(255),
    body                    TEXT NOT NULL,
    sent_by                 UUID REFERENCES users(id) ON DELETE SET NULL,
    read_at                 TIMESTAMP,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_client_message_client ON client_message(client_id);
CREATE INDEX idx_client_message_tax_return ON client_message(tax_return_id);
CREATE INDEX idx_client_message_question ON client_message(question_id);
CREATE INDEX idx_client_message_client_date ON client_message(client_id, created_at);

COMMENT ON TABLE client_message IS 'Messages exchanged with clients via portal/email/SMS';

-- -----------------------------------------------------------------------------
-- PREPARER_NOTES - Internal notes on returns
-- -----------------------------------------------------------------------------

CREATE TABLE preparer_notes (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tax_return_id           UUID NOT NULL REFERENCES tax_return(id) ON DELETE CASCADE,
    document_id             UUID REFERENCES document(id) ON DELETE SET NULL,
    created_by              UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    note_text               TEXT NOT NULL,
    note_type               note_type_enum NOT NULL,
    is_resolved             BOOLEAN DEFAULT FALSE,
    resolved_by             UUID REFERENCES users(id) ON DELETE SET NULL,
    resolved_at             TIMESTAMP,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_preparer_notes_tax_return ON preparer_notes(tax_return_id);
CREATE INDEX idx_preparer_notes_type ON preparer_notes(tax_return_id, note_type);
CREATE INDEX idx_preparer_notes_open ON preparer_notes(is_resolved, note_type) WHERE is_resolved = FALSE;

COMMENT ON TABLE preparer_notes IS 'Internal notes on tax returns (not visible to clients)';


-- =============================================================================
-- ENGAGEMENT & COMPLIANCE TABLES
-- =============================================================================

-- -----------------------------------------------------------------------------
-- FEE_SCHEDULE - Fee calculation rules per tax year (S3-001)
-- -----------------------------------------------------------------------------

CREATE TABLE fee_schedule (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name                    VARCHAR(255) NOT NULL,
    tax_year                INTEGER NOT NULL,
    base_fees               JSONB NOT NULL DEFAULT '{}',
    complexity_multipliers  JSONB NOT NULL DEFAULT '{}',
    is_active               BOOLEAN NOT NULL DEFAULT TRUE,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_fee_schedule_year ON fee_schedule(tax_year);
CREATE INDEX idx_fee_schedule_active ON fee_schedule(is_active) WHERE is_active = TRUE;

COMMENT ON TABLE fee_schedule IS 'Fee calculation rules per tax year';
COMMENT ON COLUMN fee_schedule.base_fees IS 'JSON map of service type to base fee amount';
COMMENT ON COLUMN fee_schedule.complexity_multipliers IS 'JSON map of complexity level to multiplier';

-- -----------------------------------------------------------------------------
-- ENGAGEMENT - Signed engagement letters
-- -----------------------------------------------------------------------------

CREATE TABLE engagement (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    client_id               UUID NOT NULL REFERENCES client(id) ON DELETE RESTRICT,
    tax_year                INTEGER NOT NULL,
    engagement_type         engagement_type_enum NOT NULL,
    template_id             VARCHAR(50) NOT NULL,
    status                  engagement_status_enum DEFAULT 'draft',
    generated_at            TIMESTAMP,
    sent_at                 TIMESTAMP,
    signed_at               TIMESTAMP,
    signer_name             VARCHAR(255),
    signer_email            VARCHAR(255),
    unsigned_s3_key         VARCHAR(500),
    signed_s3_key           VARCHAR(500),
    google_signature_id     VARCHAR(100),
    expires_at              DATE,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_engagement_client ON engagement(client_id);
CREATE INDEX idx_engagement_client_year ON engagement(client_id, tax_year);
CREATE INDEX idx_engagement_status ON engagement(status);

CREATE TRIGGER update_engagement_updated_at
    BEFORE UPDATE ON engagement
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE engagement IS 'Signed engagement letters for client relationships';

-- -----------------------------------------------------------------------------
-- FORM_7216_CONSENT - IRS disclosure consent tracking
-- -----------------------------------------------------------------------------

CREATE TABLE form_7216_consent (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    client_id               UUID NOT NULL REFERENCES client(id) ON DELETE RESTRICT,
    tax_year                INTEGER NOT NULL,
    consent_scope           TEXT NOT NULL,
    status                  consent_status_enum DEFAULT 'pending',
    generated_at            TIMESTAMP,
    signed_at               TIMESTAMP,
    expires_at              DATE NOT NULL,
    consent_s3_key          VARCHAR(500),
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_form_7216_client ON form_7216_consent(client_id);
CREATE INDEX idx_form_7216_client_year ON form_7216_consent(client_id, tax_year);
CREATE INDEX idx_form_7216_expiration ON form_7216_consent(status, expires_at);

CREATE TRIGGER update_form_7216_consent_updated_at
    BEFORE UPDATE ON form_7216_consent
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE form_7216_consent IS 'IRS Form 7216 consent tracking for tax information disclosure';

-- -----------------------------------------------------------------------------
-- CONSENT - General consent tracking (S3-003)
-- -----------------------------------------------------------------------------

CREATE TABLE consent (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    client_id               UUID NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    consent_type            consent_type_enum NOT NULL,
    description             TEXT NOT NULL,
    third_party_name        VARCHAR(255),
    tax_years               JSONB NOT NULL DEFAULT '[]',
    granted_at              TIMESTAMP NOT NULL,
    expires_at              TIMESTAMP,
    revoked_at              TIMESTAMP,
    signature_request_id    VARCHAR(255),
    document_s3_key         VARCHAR(500),
    created_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_consent_client_id ON consent(client_id);
CREATE INDEX idx_consent_type ON consent(consent_type);
CREATE INDEX idx_consent_expires_at ON consent(expires_at) WHERE revoked_at IS NULL;

COMMENT ON TABLE consent IS 'General consent tracking for third-party disclosure';
COMMENT ON COLUMN consent.tax_years IS 'JSON array of tax years this consent applies to';

-- -----------------------------------------------------------------------------
-- PTIN_RECORD - Preparer Tax Identification Number tracking
-- -----------------------------------------------------------------------------

CREATE TABLE ptin_record (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id                 UUID UNIQUE NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    ptin                    VARCHAR(10) NOT NULL,
    -- COMP-006: Field-level encryption column
    ptin_encrypted          BYTEA,
    issue_date              DATE,
    expiration_date         DATE NOT NULL,
    is_active               BOOLEAN DEFAULT TRUE,
    last_verified_at        TIMESTAMP,
    renewal_alert_sent      BOOLEAN DEFAULT FALSE,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_ptin_record_expiration ON ptin_record(expiration_date);

CREATE TRIGGER update_ptin_record_updated_at
    BEFORE UPDATE ON ptin_record
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE ptin_record IS 'Preparer Tax Identification Number tracking per user';
COMMENT ON COLUMN ptin_record.ptin IS 'PTIN format: Pxxxxxxxx';

-- -----------------------------------------------------------------------------
-- AUDIT_LOG - Immutable audit trail
-- Partitioned by month for 7-year retention
-- -----------------------------------------------------------------------------

CREATE TABLE audit_log (
    id                      UUID NOT NULL DEFAULT uuid_generate_v4(),
    correlation_id          UUID NOT NULL,
    actor_id                UUID,
    actor_type              actor_type_enum NOT NULL,
    action                  audit_action_enum NOT NULL,
    resource_type           VARCHAR(50) NOT NULL,
    resource_id             UUID,
    client_id               UUID,
    tax_return_id           UUID,
    before_value            JSONB,
    after_value             JSONB,
    ip_address              INET,
    user_agent              VARCHAR(500),
    outcome                 audit_outcome_enum NOT NULL,
    error_message           TEXT,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE INDEX idx_audit_log_correlation ON audit_log(correlation_id);
CREATE INDEX idx_audit_log_actor ON audit_log(actor_id);
CREATE INDEX idx_audit_log_resource ON audit_log(resource_type, resource_id);
CREATE INDEX idx_audit_log_client ON audit_log(client_id);
CREATE INDEX idx_audit_log_date ON audit_log(created_at);

COMMENT ON TABLE audit_log IS 'Immutable audit trail for all system activity - append only, no updates or deletes';
COMMENT ON COLUMN audit_log.correlation_id IS 'Request correlation ID for tracing';
COMMENT ON COLUMN audit_log.before_value IS 'State before change (for updates)';
COMMENT ON COLUMN audit_log.after_value IS 'State after change';

-- Create partitions for current and next 7 years (7-year retention)
CREATE TABLE audit_log_2024 PARTITION OF audit_log
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE audit_log_2025 PARTITION OF audit_log
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE audit_log_2026 PARTITION OF audit_log
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
CREATE TABLE audit_log_2027 PARTITION OF audit_log
    FOR VALUES FROM ('2027-01-01') TO ('2028-01-01');
CREATE TABLE audit_log_2028 PARTITION OF audit_log
    FOR VALUES FROM ('2028-01-01') TO ('2029-01-01');
CREATE TABLE audit_log_2029 PARTITION OF audit_log
    FOR VALUES FROM ('2029-01-01') TO ('2030-01-01');
CREATE TABLE audit_log_2030 PARTITION OF audit_log
    FOR VALUES FROM ('2030-01-01') TO ('2031-01-01');
CREATE TABLE audit_log_2031 PARTITION OF audit_log
    FOR VALUES FROM ('2031-01-01') TO ('2032-01-01');

-- Revoke UPDATE and DELETE on audit_log to ensure immutability
-- (Apply after table creation in production)
-- REVOKE UPDATE, DELETE ON audit_log FROM public;


-- =============================================================================
-- FINANCIAL TABLES
-- =============================================================================

-- -----------------------------------------------------------------------------
-- INVOICE_NUMBER_SEQ - Sequential counter for generating invoice numbers
-- Used by InvoiceRepository to generate unique invoice numbers in format INV-YYYYMMDD-NNNN
-- -----------------------------------------------------------------------------

CREATE TABLE invoice_number_seq (
    date_prefix             VARCHAR(8) PRIMARY KEY,  -- YYYYMMDD format
    next_val                INTEGER NOT NULL DEFAULT 1,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE invoice_number_seq IS 'Sequential counter for generating unique invoice numbers';

-- -----------------------------------------------------------------------------
-- INVOICE - Client billing records
-- -----------------------------------------------------------------------------

CREATE TABLE invoice (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    client_id               UUID NOT NULL REFERENCES client(id) ON DELETE RESTRICT,
    tax_return_id           UUID REFERENCES tax_return(id) ON DELETE SET NULL,
    invoice_number          VARCHAR(20) UNIQUE NOT NULL,
    tax_year                INTEGER,
    invoice_date            DATE NOT NULL,
    due_date                DATE NOT NULL,
    base_fee                DECIMAL(10,2) NOT NULL,
    complexity_adjustment   DECIMAL(10,2) DEFAULT 0,
    rush_fee                DECIMAL(10,2) DEFAULT 0,
    discount                DECIMAL(10,2) DEFAULT 0,
    subtotal                DECIMAL(10,2) NOT NULL,
    tax_amount              DECIMAL(10,2) DEFAULT 0,
    total_amount            DECIMAL(10,2) NOT NULL,
    amount_paid             DECIMAL(10,2) DEFAULT 0,
    balance_due             DECIMAL(10,2) GENERATED ALWAYS AS (total_amount - amount_paid) STORED,
    status                  invoice_status_enum DEFAULT 'draft',
    sent_at                 TIMESTAMPTZ,
    viewed_at               TIMESTAMPTZ,
    paid_at                 TIMESTAMPTZ,
    stripe_invoice_id       VARCHAR(100),
    stripe_checkout_url     VARCHAR(500),
    pdf_s3_key              VARCHAR(500),
    reminder_count          INTEGER DEFAULT 0,
    last_reminder_at        TIMESTAMPTZ,
    escalated               BOOLEAN DEFAULT FALSE,
    escalated_at            TIMESTAMPTZ,
    notes                   TEXT,
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_invoice_client ON invoice(client_id);
CREATE INDEX idx_invoice_tax_return ON invoice(tax_return_id);
CREATE INDEX idx_invoice_status ON invoice(status);
CREATE INDEX idx_invoice_overdue ON invoice(status, due_date) WHERE status NOT IN ('paid', 'void');

CREATE TRIGGER update_invoice_updated_at
    BEFORE UPDATE ON invoice
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE invoice IS 'Client billing records';
COMMENT ON COLUMN invoice.balance_due IS 'Computed column: total_amount - amount_paid';

-- -----------------------------------------------------------------------------
-- INVOICE_LINE_ITEM - Individual service lines on an invoice
-- -----------------------------------------------------------------------------

CREATE TABLE invoice_line_item (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    invoice_id              UUID NOT NULL REFERENCES invoice(id) ON DELETE CASCADE,
    description             VARCHAR(500) NOT NULL,
    service_type            service_type_enum NOT NULL,
    quantity                INTEGER NOT NULL DEFAULT 1,
    unit_price              DECIMAL(10,2) NOT NULL,
    amount                  DECIMAL(10,2) NOT NULL,
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_invoice_line_item_invoice ON invoice_line_item(invoice_id);

COMMENT ON TABLE invoice_line_item IS 'Individual service lines on an invoice';

-- -----------------------------------------------------------------------------
-- PAYMENT - Payment records linked to Stripe
-- -----------------------------------------------------------------------------

CREATE TABLE payment (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    invoice_id              UUID NOT NULL REFERENCES invoice(id) ON DELETE RESTRICT,
    amount                  DECIMAL(10,2) NOT NULL,
    payment_method          payment_method_enum NOT NULL,
    status                  payment_status_enum DEFAULT 'pending',
    stripe_payment_id       VARCHAR(100),
    stripe_charge_id        VARCHAR(100),
    payment_date            TIMESTAMPTZ,
    confirmation_number     VARCHAR(50),
    refund_amount           DECIMAL(10,2),
    refund_date             TIMESTAMPTZ,
    refund_reason           TEXT,
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_payment_invoice ON payment(invoice_id);
CREATE INDEX idx_payment_stripe ON payment(stripe_payment_id);
CREATE INDEX idx_payment_status ON payment(status);

CREATE TRIGGER update_payment_updated_at
    BEFORE UPDATE ON payment
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE payment IS 'Payment records linked to Stripe';

-- -----------------------------------------------------------------------------
-- ESTIMATED_TAX_PAYMENT - Quarterly estimated tax tracking
-- -----------------------------------------------------------------------------

CREATE TABLE estimated_tax_payment (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    client_id               UUID NOT NULL REFERENCES client(id) ON DELETE RESTRICT,
    tax_return_id           UUID NOT NULL REFERENCES tax_return(id) ON DELETE CASCADE,
    tax_year                INTEGER NOT NULL,
    quarter                 INTEGER NOT NULL CHECK (quarter >= 1 AND quarter <= 4),
    due_date                DATE NOT NULL,
    federal_amount          DECIMAL(10,2),
    state_code              VARCHAR(2),
    state_amount            DECIMAL(10,2),
    total_amount            DECIMAL(10,2) NOT NULL,
    voucher_s3_key          VARCHAR(500),
    state_voucher_s3_key    VARCHAR(500),
    status                  estimated_payment_status_enum DEFAULT 'scheduled',
    confirmed_paid_at       TIMESTAMP,
    payment_confirmation    VARCHAR(100),
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_estimated_tax_client ON estimated_tax_payment(client_id);
CREATE INDEX idx_estimated_tax_return ON estimated_tax_payment(tax_return_id);
CREATE INDEX idx_estimated_tax_year_quarter ON estimated_tax_payment(tax_year, quarter);
CREATE INDEX idx_estimated_tax_status_due ON estimated_tax_payment(status, due_date);

CREATE TRIGGER update_estimated_tax_payment_updated_at
    BEFORE UPDATE ON estimated_tax_payment
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE estimated_tax_payment IS 'Quarterly estimated tax payment tracking';

-- -----------------------------------------------------------------------------
-- ESTIMATED_TAX_REMINDER - Reminders for estimated payments
-- -----------------------------------------------------------------------------

CREATE TABLE estimated_tax_reminder (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    estimated_payment_id    UUID NOT NULL REFERENCES estimated_tax_payment(id) ON DELETE CASCADE,
    reminder_type           reminder_type_enum NOT NULL,
    channel                 communication_channel_enum NOT NULL,
    sent_at                 TIMESTAMP NOT NULL DEFAULT NOW(),
    email_sent              BOOLEAN DEFAULT FALSE,
    sms_sent                BOOLEAN DEFAULT FALSE,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_estimated_tax_reminder_payment ON estimated_tax_reminder(estimated_payment_id);
CREATE INDEX idx_estimated_tax_reminder_type ON estimated_tax_reminder(reminder_type, sent_at);

COMMENT ON TABLE estimated_tax_reminder IS 'Reminders sent for estimated tax payments';


-- =============================================================================
-- DELIVERY TABLES (S9)
-- =============================================================================

-- -----------------------------------------------------------------------------
-- DELIVERY_PACKAGE - Client return delivery packages
-- Status: draft → generated → sent → partially_signed → signed → ready
-- -----------------------------------------------------------------------------

-- Package status enum: tracks delivery package lifecycle
CREATE TYPE package_status_enum AS ENUM (
    'draft',             -- Package being prepared
    'generated',         -- PDF generated
    'sent',              -- Sent to client
    'partially_signed',  -- Some signatures collected
    'signed',            -- All signatures collected
    'ready'              -- Ready for filing
);

CREATE TABLE delivery_package (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    -- The tax return this package is for
    tax_return_id           UUID NOT NULL,
    -- Client receiving the package
    client_id               UUID NOT NULL REFERENCES client(id),
    -- Tax year for this package
    tax_year                INT NOT NULL DEFAULT 2024,
    -- Current status in the delivery lifecycle
    status                  package_status_enum NOT NULL DEFAULT 'draft',
    -- URL to the generated PDF (may be presigned S3 URL)
    pdf_url                 TEXT,
    -- S3 key for the PDF document
    pdf_s3_key              TEXT,
    -- Total pages in the package
    total_pages             INT DEFAULT 0,
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    -- When package was sent to client
    sent_at                 TIMESTAMPTZ,
    -- When all signatures collected
    completed_at            TIMESTAMPTZ
);

CREATE UNIQUE INDEX idx_delivery_package_return ON delivery_package(tax_return_id);
CREATE INDEX idx_delivery_package_client ON delivery_package(client_id);
CREATE INDEX idx_delivery_package_status ON delivery_package(status);

COMMENT ON TABLE delivery_package IS 'Client return delivery packages for signature collection';

-- -----------------------------------------------------------------------------
-- PAYMENT_AUTHORIZATION - Payment authorization for delivery packages
-- Links to Stripe for payment processing before e-filing
-- -----------------------------------------------------------------------------

CREATE TABLE payment_authorization (
    id                          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    -- The delivery package this payment is for
    package_id                  UUID NOT NULL REFERENCES delivery_package(id),
    -- Client making the payment
    client_id                   UUID NOT NULL REFERENCES client(id),
    -- Invoice amount to be paid
    invoice_amount              DECIMAL(10,2) NOT NULL,
    -- Stripe payment intent ID
    stripe_payment_intent_id    VARCHAR(100),
    -- Stripe checkout URL for client payment
    stripe_checkout_url         TEXT,
    -- Stripe checkout session ID
    stripe_checkout_session_id  VARCHAR(100),
    -- Payment status
    status                      payment_status_enum NOT NULL DEFAULT 'pending',
    -- Payment method used (card or ach)
    payment_method              payment_method_enum,
    -- Reason for payment failure if any
    failure_reason              TEXT,
    -- When payment was authorized
    authorized_at               TIMESTAMPTZ,
    -- When payment was completed
    paid_at                     TIMESTAMPTZ,
    created_at                  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_payment_auth_package ON payment_authorization(package_id);
CREATE INDEX idx_payment_auth_client ON payment_authorization(client_id);
CREATE INDEX idx_payment_auth_intent ON payment_authorization(stripe_payment_intent_id);
CREATE INDEX idx_payment_auth_session ON payment_authorization(stripe_checkout_session_id);
CREATE INDEX idx_payment_auth_status ON payment_authorization(status);

COMMENT ON TABLE payment_authorization IS 'Payment authorization records for delivery packages';


-- =============================================================================
-- E-FILING TABLES
-- =============================================================================

-- -----------------------------------------------------------------------------
-- TRANSMITTER_ACCOUNT - E-filing transmitter configuration
-- -----------------------------------------------------------------------------

CREATE TABLE transmitter_account (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    transmitter_name        VARCHAR(100) NOT NULL,
    efin                    VARCHAR(10) NOT NULL,
    api_endpoint            VARCHAR(255) NOT NULL,
    is_active               BOOLEAN DEFAULT TRUE,
    is_primary              BOOLEAN DEFAULT FALSE,
    configuration           JSONB,
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_transmitter_active ON transmitter_account(is_active);
CREATE INDEX idx_transmitter_primary ON transmitter_account(is_primary);

CREATE TRIGGER update_transmitter_account_updated_at
    BEFORE UPDATE ON transmitter_account
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE transmitter_account IS 'E-filing transmitter configuration (credentials stored in Secrets Manager)';

-- -----------------------------------------------------------------------------
-- TRANSMITTER_STATE_COVERAGE - State e-file capabilities
-- -----------------------------------------------------------------------------

CREATE TABLE transmitter_state_coverage (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    transmitter_id          UUID NOT NULL REFERENCES transmitter_account(id) ON DELETE CASCADE,
    state_code              VARCHAR(2) NOT NULL,
    return_types            return_type_enum[] NOT NULL,
    is_active               BOOLEAN DEFAULT TRUE,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW(),

    CONSTRAINT uq_transmitter_state UNIQUE (transmitter_id, state_code)
);

CREATE INDEX idx_transmitter_coverage_transmitter ON transmitter_state_coverage(transmitter_id);
CREATE INDEX idx_transmitter_coverage_state ON transmitter_state_coverage(state_code);

CREATE TRIGGER update_transmitter_state_coverage_updated_at
    BEFORE UPDATE ON transmitter_state_coverage
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE transmitter_state_coverage IS 'State e-file capabilities per transmitter';

-- -----------------------------------------------------------------------------
-- EFILING_TRANSMISSION - E-file submission records
-- -----------------------------------------------------------------------------

CREATE TABLE efiling_transmission (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tax_return_id           UUID NOT NULL REFERENCES tax_return(id) ON DELETE RESTRICT,
    transmitter_account_id  UUID NOT NULL REFERENCES transmitter_account(id) ON DELETE RESTRICT,
    transmission_type       transmission_type_enum NOT NULL,
    status                  transmission_status_enum DEFAULT 'queued',
    queued_at               TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    transmitted_at          TIMESTAMPTZ,
    federal_submission_id   VARCHAR(50),
    state_submissions       JSONB,
    error_message           TEXT,
    retry_count             INTEGER DEFAULT 0,
    -- Filing override fields: tracks when e-filing authorized before payment complete
    filing_override         BOOLEAN NOT NULL DEFAULT FALSE,
    filing_override_by      UUID REFERENCES users(id) ON DELETE SET NULL,
    filing_override_reason  VARCHAR(500),
    filing_override_at      TIMESTAMPTZ,
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_efiling_transmission_return ON efiling_transmission(tax_return_id);
CREATE INDEX idx_efiling_transmission_status ON efiling_transmission(status);
CREATE INDEX idx_efiling_transmission_date ON efiling_transmission(transmitted_at);

CREATE TRIGGER update_efiling_transmission_updated_at
    BEFORE UPDATE ON efiling_transmission
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE efiling_transmission IS 'E-file submission record';
COMMENT ON COLUMN efiling_transmission.state_submissions IS 'JSON: {state_code: submission_id, ...}';
COMMENT ON COLUMN efiling_transmission.filing_override IS 'True when e-filing authorized before payment complete (pro bono, payment arrangements)';

-- -----------------------------------------------------------------------------
-- EFILING_ACKNOWLEDGEMENT - IRS/State acknowledgements
-- -----------------------------------------------------------------------------

CREATE TABLE efiling_acknowledgement (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    transmission_id         UUID NOT NULL REFERENCES efiling_transmission(id) ON DELETE CASCADE,
    jurisdiction            VARCHAR(10) NOT NULL,
    ack_type                ack_type_enum NOT NULL,
    ack_date                TIMESTAMPTZ NOT NULL,
    confirmation_number     VARCHAR(50),
    irs_submission_id       VARCHAR(50),
    raw_response            JSONB,
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_efiling_ack_transmission ON efiling_acknowledgement(transmission_id);
CREATE INDEX idx_efiling_ack_jurisdiction ON efiling_acknowledgement(jurisdiction, ack_type);
CREATE INDEX idx_efiling_ack_date ON efiling_acknowledgement(ack_date);

COMMENT ON TABLE efiling_acknowledgement IS 'IRS/State acknowledgement of e-filed return';
COMMENT ON COLUMN efiling_acknowledgement.jurisdiction IS 'federal or state code (e.g., CA, NY)';

-- -----------------------------------------------------------------------------
-- EFILING_REJECTION - Rejection details
-- -----------------------------------------------------------------------------

CREATE TABLE efiling_rejection (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    acknowledgement_id      UUID NOT NULL REFERENCES efiling_acknowledgement(id) ON DELETE CASCADE,
    error_code              VARCHAR(20) NOT NULL,
    error_category          rejection_category_enum NOT NULL,
    error_message           TEXT NOT NULL,
    field_name              VARCHAR(100),
    is_auto_correctable     BOOLEAN DEFAULT FALSE,
    resolution_status       resolution_status_enum DEFAULT 'pending',
    assigned_to             UUID REFERENCES users(id) ON DELETE SET NULL,
    resolution_notes        TEXT,
    resolved_at             TIMESTAMPTZ,
    resolved_by             UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_efiling_rejection_ack ON efiling_rejection(acknowledgement_id);
CREATE INDEX idx_efiling_rejection_category ON efiling_rejection(error_category);
CREATE INDEX idx_efiling_rejection_status ON efiling_rejection(resolution_status);
CREATE INDEX idx_efiling_rejection_assigned ON efiling_rejection(assigned_to);

CREATE TRIGGER update_efiling_rejection_updated_at
    BEFORE UPDATE ON efiling_rejection
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE efiling_rejection IS 'Details of e-file rejection';

-- -----------------------------------------------------------------------------
-- EXTENSION - Form 4868 extension filing
-- -----------------------------------------------------------------------------

CREATE TABLE extension (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tax_return_id           UUID NOT NULL REFERENCES tax_return(id) ON DELETE CASCADE,
    client_id               UUID NOT NULL REFERENCES client(id) ON DELETE RESTRICT,
    tax_year                INTEGER NOT NULL,
    original_due_date       DATE NOT NULL,
    extended_due_date       DATE NOT NULL,
    estimated_tax_liability DECIMAL(12,2),
    payment_amount          DECIMAL(12,2),
    form_s3_key             VARCHAR(500),
    status                  extension_status_enum DEFAULT 'draft',
    filed_at                TIMESTAMP,
    confirmation_number     VARCHAR(50),
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_extension_return ON extension(tax_return_id);
CREATE INDEX idx_extension_client ON extension(client_id);
CREATE INDEX idx_extension_year_status ON extension(tax_year, status);

CREATE TRIGGER update_extension_updated_at
    BEFORE UPDATE ON extension
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE extension IS 'Form 4868 extension filing';


-- =============================================================================
-- IDENTITY VERIFICATION TABLES
-- =============================================================================

-- -----------------------------------------------------------------------------
-- IDENTITY_VERIFICATION - Client identity verification records
-- -----------------------------------------------------------------------------

CREATE TABLE identity_verification (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    client_id               UUID NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    verification_tier       verification_tier_enum NOT NULL,
    status                  id_verification_status_enum DEFAULT 'initiated',
    initiated_at            TIMESTAMP NOT NULL DEFAULT NOW(),
    completed_at            TIMESTAMP,
    method                  verification_method_enum,
    persona_inquiry_id      VARCHAR(100),
    persona_confidence_score DECIMAL(3,2) CHECK (persona_confidence_score >= 0 AND persona_confidence_score <= 1),
    fraud_indicators        JSONB,
    verified_by             UUID REFERENCES users(id) ON DELETE SET NULL,
    notes                   TEXT,
    expires_at              TIMESTAMP,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_identity_verification_client ON identity_verification(client_id);
CREATE INDEX idx_identity_verification_status ON identity_verification(status);
CREATE INDEX idx_identity_verification_client_tier ON identity_verification(client_id, verification_tier);

CREATE TRIGGER update_identity_verification_updated_at
    BEFORE UPDATE ON identity_verification
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE identity_verification IS 'Client identity verification record';
COMMENT ON COLUMN identity_verification.persona_confidence_score IS 'Persona.com verification confidence (0.00-1.00)';

-- -----------------------------------------------------------------------------
-- MAGIC_LINK - Passwordless authentication tokens
-- -----------------------------------------------------------------------------

CREATE TABLE magic_link (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    client_id               UUID NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    token                   VARCHAR(64) NOT NULL UNIQUE,
    email_sent_to           VARCHAR(255) NOT NULL,
    status                  magic_link_status_enum DEFAULT 'pending',
    expires_at              TIMESTAMP NOT NULL,
    used_at                 TIMESTAMP,
    used_from_ip            INET,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_magic_link_token ON magic_link(token);
CREATE INDEX idx_magic_link_client ON magic_link(client_id);
CREATE INDEX idx_magic_link_status ON magic_link(status) WHERE status = 'pending';

COMMENT ON TABLE magic_link IS 'Passwordless authentication tokens for client portal access';

-- -----------------------------------------------------------------------------
-- LOGIN_ATTEMPT - Security tracking for authentication attempts
-- -----------------------------------------------------------------------------

CREATE TABLE login_attempt (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    client_id               UUID REFERENCES client(id) ON DELETE SET NULL,
    email                   VARCHAR(255) NOT NULL,
    ip_address              INET,
    user_agent              VARCHAR(500),
    result                  VARCHAR(50) NOT NULL,  -- 'success', 'failure', 'failed_client_not_found', etc.
    failure_reason          VARCHAR(100),
    created_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_login_attempt_client ON login_attempt(client_id);
CREATE INDEX idx_login_attempt_email ON login_attempt(email);
CREATE INDEX idx_login_attempt_ip ON login_attempt(ip_address);
CREATE INDEX idx_login_attempt_recent ON login_attempt(email, created_at DESC);

COMMENT ON TABLE login_attempt IS 'Security tracking for login attempts - used for rate limiting and lockout';

-- -----------------------------------------------------------------------------
-- SECURITY_QUESTION - Fallback security questions for login (S2-002)
-- -----------------------------------------------------------------------------

CREATE TABLE security_question (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    client_id               UUID NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    question                VARCHAR(500) NOT NULL,
    answer_hash             VARCHAR(255) NOT NULL,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_security_question_client_id ON security_question(client_id);

CREATE TRIGGER update_security_question_updated_at
    BEFORE UPDATE ON security_question
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE security_question IS 'Security questions for client login fallback authentication';
COMMENT ON COLUMN security_question.answer_hash IS 'Bcrypt hash of security answer';

-- -----------------------------------------------------------------------------
-- EMAIL_VERIFICATION - Email verification tokens for client onboarding
-- -----------------------------------------------------------------------------

CREATE TABLE email_verification (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    client_id               UUID NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    token                   VARCHAR(64) NOT NULL UNIQUE,
    email                   VARCHAR(255),  -- Optional, can be looked up from client
    status                  email_verification_status_enum DEFAULT 'pending',
    expires_at              TIMESTAMP NOT NULL,
    verified_at             TIMESTAMP,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_email_verification_token ON email_verification(token);
CREATE INDEX idx_email_verification_client ON email_verification(client_id);
CREATE INDEX idx_email_verification_status ON email_verification(status) WHERE status = 'pending';

COMMENT ON TABLE email_verification IS 'Email verification tokens for client onboarding';

-- -----------------------------------------------------------------------------
-- GOOGLE_MEET_SESSION - Video verification sessions for Tier 3
-- -----------------------------------------------------------------------------

CREATE TABLE google_meet_session (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    verification_id         UUID NOT NULL REFERENCES identity_verification(id) ON DELETE CASCADE,
    client_id               UUID NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    scheduled_at            TIMESTAMP NOT NULL,
    meet_link               VARCHAR(255) NOT NULL,
    calendar_event_id       VARCHAR(100),
    status                  meet_session_status_enum DEFAULT 'scheduled',
    conducted_by            UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    started_at              TIMESTAMP,
    ended_at                TIMESTAMP,
    recording_drive_id      VARCHAR(100),
    outcome                 meet_outcome_enum,
    notes                   TEXT,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_google_meet_verification ON google_meet_session(verification_id);
CREATE INDEX idx_google_meet_client ON google_meet_session(client_id);
CREATE INDEX idx_google_meet_scheduled ON google_meet_session(scheduled_at);
CREATE INDEX idx_google_meet_conducted_by ON google_meet_session(conducted_by);

CREATE TRIGGER update_google_meet_session_updated_at
    BEFORE UPDATE ON google_meet_session
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE google_meet_session IS 'Video verification session for Tier 3 identity verification';


-- =============================================================================
-- INTEGRATION TABLES
-- =============================================================================

-- -----------------------------------------------------------------------------
-- SMARTVAULT_DOCUMENT - Cached SmartVault document metadata
-- -----------------------------------------------------------------------------

CREATE TABLE smartvault_document (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    document_id             UUID REFERENCES document(id) ON DELETE SET NULL,
    smartvault_id           VARCHAR(100) UNIQUE NOT NULL,
    folder_path             VARCHAR(500) NOT NULL,
    filename                VARCHAR(255) NOT NULL,
    file_size               BIGINT,
    smartvault_modified_at  TIMESTAMP,
    sync_status             sync_status_enum DEFAULT 'synced',
    last_synced_at          TIMESTAMP,
    sync_error              TEXT,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_smartvault_document_doc ON smartvault_document(document_id);
CREATE INDEX idx_smartvault_document_folder ON smartvault_document(folder_path);
CREATE INDEX idx_smartvault_document_sync ON smartvault_document(sync_status);

CREATE TRIGGER update_smartvault_document_updated_at
    BEFORE UPDATE ON smartvault_document
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE smartvault_document IS 'Cached SmartVault document metadata';

-- -----------------------------------------------------------------------------
-- SMARTVAULT_FOLDER - SmartVault folder structure cache
-- -----------------------------------------------------------------------------

CREATE TABLE smartvault_folder (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    client_id               UUID NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    smartvault_folder_id    VARCHAR(100) UNIQUE NOT NULL,
    folder_path             VARCHAR(500) NOT NULL,
    folder_name             VARCHAR(255) NOT NULL,
    folder_type             folder_type_enum,
    tax_year                INTEGER,
    parent_folder_id        UUID REFERENCES smartvault_folder(id) ON DELETE SET NULL,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_smartvault_folder_client ON smartvault_folder(client_id);
CREATE INDEX idx_smartvault_folder_client_year ON smartvault_folder(client_id, tax_year);

CREATE TRIGGER update_smartvault_folder_updated_at
    BEFORE UPDATE ON smartvault_folder
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE smartvault_folder IS 'SmartVault folder structure cache';

-- -----------------------------------------------------------------------------
-- SUREPREP_BINDER - SurePrep processing binder
-- -----------------------------------------------------------------------------

CREATE TABLE sureprep_binder (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tax_return_id           UUID NOT NULL REFERENCES tax_return(id) ON DELETE CASCADE,
    sureprep_binder_id      VARCHAR(100) UNIQUE NOT NULL,
    binder_status           binder_status_enum DEFAULT 'created',
    submitted_at            TIMESTAMP,
    processing_complete_at  TIMESTAMP,
    export_complete_at      TIMESTAMP,
    document_count          INTEGER DEFAULT 0,
    error_message           TEXT,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_sureprep_binder_return ON sureprep_binder(tax_return_id);
CREATE INDEX idx_sureprep_binder_status ON sureprep_binder(binder_status);

CREATE TRIGGER update_sureprep_binder_updated_at
    BEFORE UPDATE ON sureprep_binder
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE sureprep_binder IS 'SurePrep processing binder';

-- Add FK from document_extraction to sureprep_binder now that table exists
ALTER TABLE document_extraction
    ADD CONSTRAINT fk_extraction_sureprep_binder
    FOREIGN KEY (sureprep_binder_id) REFERENCES sureprep_binder(id) ON DELETE SET NULL;

-- -----------------------------------------------------------------------------
-- SUREPREP_EXTRACTION - Extraction results from SurePrep
-- -----------------------------------------------------------------------------

CREATE TABLE sureprep_extraction (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    binder_id               UUID NOT NULL REFERENCES sureprep_binder(id) ON DELETE CASCADE,
    document_id             UUID NOT NULL REFERENCES document(id) ON DELETE CASCADE,
    sureprep_doc_id         VARCHAR(100) NOT NULL,
    document_type           document_type_enum NOT NULL,
    extraction_status       sureprep_extraction_status_enum NOT NULL,
    confidence_score        DECIMAL(3,2) CHECK (confidence_score >= 0 AND confidence_score <= 1),
    is_native_pdf           BOOLEAN,
    extracted_fields        JSONB,
    extracted_at            TIMESTAMP,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_sureprep_extraction_binder ON sureprep_extraction(binder_id);
CREATE INDEX idx_sureprep_extraction_document ON sureprep_extraction(document_id);
CREATE INDEX idx_sureprep_extraction_status ON sureprep_extraction(extraction_status);

CREATE TRIGGER update_sureprep_extraction_updated_at
    BEFORE UPDATE ON sureprep_extraction
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE sureprep_extraction IS 'Extraction result from SurePrep';

-- -----------------------------------------------------------------------------
-- GOOGLE_SIGNATURE_REQUEST - E-signature requests via Google Workspace
-- -----------------------------------------------------------------------------

CREATE TABLE google_signature_request (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tax_return_id           UUID NOT NULL REFERENCES tax_return(id) ON DELETE CASCADE,
    document_id             UUID NOT NULL REFERENCES document(id) ON DELETE CASCADE,
    document_type           signature_doc_type_enum NOT NULL,
    signer_email            VARCHAR(255) NOT NULL,
    signer_name             VARCHAR(255) NOT NULL,
    google_doc_id           VARCHAR(100),
    signature_status        signature_status_enum DEFAULT 'pending',
    sent_at                 TIMESTAMP,
    viewed_at               TIMESTAMP,
    signed_at               TIMESTAMP,
    signed_document_s3_key  VARCHAR(500),
    reminder_count          INTEGER DEFAULT 0,
    last_reminder_at        TIMESTAMP,
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_google_signature_return ON google_signature_request(tax_return_id);
CREATE INDEX idx_google_signature_document ON google_signature_request(document_id);
CREATE INDEX idx_google_signature_status ON google_signature_request(signature_status);
CREATE INDEX idx_google_signature_signer ON google_signature_request(signer_email);

CREATE TRIGGER update_google_signature_request_updated_at
    BEFORE UPDATE ON google_signature_request
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE google_signature_request IS 'Google Workspace e-signature request tracking';

-- -----------------------------------------------------------------------------
-- STRIPE_PAYMENT_INTENT - Stripe payment intent tracking
-- -----------------------------------------------------------------------------

CREATE TABLE stripe_payment_intent (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    invoice_id              UUID NOT NULL REFERENCES invoice(id) ON DELETE RESTRICT,
    stripe_id               VARCHAR(100) UNIQUE NOT NULL,
    amount_cents            INTEGER NOT NULL,
    currency                VARCHAR(3) DEFAULT 'usd',
    status                  stripe_status_enum NOT NULL,
    payment_method_type     VARCHAR(50),
    receipt_url             VARCHAR(500),
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_stripe_payment_intent_invoice ON stripe_payment_intent(invoice_id);
CREATE INDEX idx_stripe_payment_intent_status ON stripe_payment_intent(status);

CREATE TRIGGER update_stripe_payment_intent_updated_at
    BEFORE UPDATE ON stripe_payment_intent
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE stripe_payment_intent IS 'Stripe payment intent tracking';

-- -----------------------------------------------------------------------------
-- AMENDED_RETURN - Form 1040-X tracking
-- -----------------------------------------------------------------------------

CREATE TABLE amended_return (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    original_return_id      UUID NOT NULL REFERENCES tax_return(id) ON DELETE RESTRICT,
    amended_return_id       UUID NOT NULL REFERENCES tax_return(id) ON DELETE CASCADE,
    client_id               UUID NOT NULL REFERENCES client(id) ON DELETE RESTRICT,
    tax_year                INTEGER NOT NULL,
    amendment_reason        TEXT NOT NULL,
    original_refund         DECIMAL(12,2),
    amended_refund          DECIMAL(12,2),
    difference              DECIMAL(12,2),
    created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_amended_return_original ON amended_return(original_return_id);
CREATE INDEX idx_amended_return_amended ON amended_return(amended_return_id);
CREATE INDEX idx_amended_return_client ON amended_return(client_id);

CREATE TRIGGER update_amended_return_updated_at
    BEFORE UPDATE ON amended_return
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE amended_return IS 'Form 1040-X amended return tracking - links original to amended';


-- =============================================================================
-- AI CHAT TABLES (S13-001)
-- =============================================================================
-- AI-assisted chat functionality for tax assistance.
-- Separate from messaging tables which handle human-to-human communication.
-- Sessions are scoped to single client for security.
-- =============================================================================

-- -----------------------------------------------------------------------------
-- Enum: Chat message role
-- -----------------------------------------------------------------------------

CREATE TYPE chat_role_enum AS ENUM (
    'user',       -- Human user (staff or client)
    'assistant',  -- AI assistant response
    'system'      -- System instructions/context
);

COMMENT ON TYPE chat_role_enum IS 'Role of sender in AI chat conversation';

-- -----------------------------------------------------------------------------
-- Enum: Chat session status
-- -----------------------------------------------------------------------------

CREATE TYPE chat_session_status_enum AS ENUM (
    'active',   -- Session is active and accepting messages
    'closed',   -- Session closed by user
    'expired'   -- Session expired due to inactivity
);

COMMENT ON TYPE chat_session_status_enum IS 'Status of AI chat session';

-- -----------------------------------------------------------------------------
-- CHAT_SESSION - AI chat session scoped to single client
-- -----------------------------------------------------------------------------

CREATE TABLE chat_session (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Ownership and scope (single-client enforcement)
    user_id                 UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    client_id               UUID NOT NULL REFERENCES client(id) ON DELETE CASCADE,
    tax_return_id           UUID REFERENCES tax_return(id) ON DELETE SET NULL,

    -- Session state
    status                  chat_session_status_enum DEFAULT 'active',

    -- Aggregated usage tracking for cost monitoring
    total_input_tokens      INTEGER DEFAULT 0,
    total_output_tokens     INTEGER DEFAULT 0,
    total_cost_usd          DECIMAL(10,6) DEFAULT 0,
    message_count           INTEGER DEFAULT 0,

    -- Context caching (built once, refreshed periodically)
    -- Contains client/return summary for AI system prompt
    context_snapshot        TEXT,
    context_built_at        TIMESTAMPTZ,

    -- Timestamps (using TIMESTAMPTZ for timezone-aware Python datetimes)
    last_message_at         TIMESTAMPTZ,
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Indexes for common access patterns
CREATE INDEX idx_chat_session_user ON chat_session(user_id);
CREATE INDEX idx_chat_session_client ON chat_session(client_id);
CREATE INDEX idx_chat_session_return ON chat_session(tax_return_id);
CREATE INDEX idx_chat_session_status ON chat_session(status) WHERE status = 'active';
CREATE INDEX idx_chat_session_user_client ON chat_session(user_id, client_id, status);

CREATE TRIGGER update_chat_session_updated_at
    BEFORE UPDATE ON chat_session
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE chat_session IS 'AI chat session scoped to single client for tax assistance';
COMMENT ON COLUMN chat_session.context_snapshot IS 'Cached client context for AI system prompt';
COMMENT ON COLUMN chat_session.total_cost_usd IS 'Aggregated cost of AI calls for this session';

-- -----------------------------------------------------------------------------
-- CHAT_MESSAGE - Individual messages in chat session
-- -----------------------------------------------------------------------------

CREATE TABLE chat_message (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    session_id              UUID NOT NULL REFERENCES chat_session(id) ON DELETE CASCADE,

    -- Message content
    role                    chat_role_enum NOT NULL,
    content                 TEXT NOT NULL,

    -- Token tracking (for assistant messages only)
    input_tokens            INTEGER,
    output_tokens           INTEGER,
    cost_usd                DECIMAL(10,6),
    model_id                VARCHAR(100),
    response_time_ms        INTEGER,

    -- Timestamp (using TIMESTAMPTZ for timezone-aware Python datetimes)
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Indexes for efficient message retrieval
CREATE INDEX idx_chat_message_session ON chat_message(session_id);
CREATE INDEX idx_chat_message_session_created ON chat_message(session_id, created_at);

COMMENT ON TABLE chat_message IS 'Individual message in AI chat session';
COMMENT ON COLUMN chat_message.input_tokens IS 'Input tokens consumed (assistant messages only)';
COMMENT ON COLUMN chat_message.output_tokens IS 'Output tokens generated (assistant messages only)';
COMMENT ON COLUMN chat_message.cost_usd IS 'Cost in USD for this message';
COMMENT ON COLUMN chat_message.model_id IS 'AI model used (e.g., claude-3-5-sonnet)';
COMMENT ON COLUMN chat_message.response_time_ms IS 'Response generation time in milliseconds';


-- =============================================================================
-- ADDITIONAL INDEXES FOR COMMON QUERIES
-- =============================================================================

-- Soft delete filters (active records only)
CREATE INDEX idx_client_active ON client(id) WHERE deleted_at IS NULL;
CREATE INDEX idx_tax_return_active ON tax_return(id) WHERE deleted_at IS NULL;
CREATE INDEX idx_document_active ON document(id) WHERE deleted_at IS NULL;

-- Dashboard queries
CREATE INDEX idx_tax_return_dashboard ON tax_return(tax_year, assigned_preparer_id, deleted_at)
    WHERE deleted_at IS NULL;

-- Deadline tracking
CREATE INDEX idx_tax_return_due_dates ON tax_return(due_date, extended_due_date)
    WHERE deleted_at IS NULL AND filed_date IS NULL;

-- Overdue invoice tracking
CREATE INDEX idx_invoice_overdue_full ON invoice(due_date, status)
    WHERE status NOT IN ('paid', 'void', 'draft');


-- =============================================================================
-- GRANTS (Apply in production - adjust role names as needed)
-- =============================================================================

-- Example: Grant to application role
-- GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO tax_app_role;
-- GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO tax_app_role;

-- Restrict audit_log modifications (append-only)
-- REVOKE UPDATE, DELETE ON audit_log FROM tax_app_role;
-- GRANT INSERT, SELECT ON audit_log TO tax_app_role;


-- =============================================================================
-- SCHEMA VERSION TRACKING
-- =============================================================================

CREATE TABLE IF NOT EXISTS schema_version (
    version         INTEGER PRIMARY KEY,
    description     TEXT NOT NULL,
    applied_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

INSERT INTO schema_version (version, description) VALUES
    (1, 'Initial schema - Tax Practice AI v1.0'),
    (2, 'Add AI chat tables (S13-001) - chat_session, chat_message');


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

COMMENT ON DATABASE tax_practice IS 'Tax Practice AI - Aurora PostgreSQL Database';
