BEGIN;

CREATE SCHEMA IF NOT EXISTS crm;

ALTER TABLE crm.customers
    ADD COLUMN IF NOT EXISTS industry varchar(120),
    ADD COLUMN IF NOT EXISTS status varchar(40) NOT NULL DEFAULT 'active',
    ADD COLUMN IF NOT EXISTS status_reason text,
    ADD COLUMN IF NOT EXISTS credit_status varchar(40) NOT NULL DEFAULT 'good',
    ADD COLUMN IF NOT EXISTS credit_used numeric(18,2) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS credit_hold_enabled boolean NOT NULL DEFAULT false,
    ADD COLUMN IF NOT EXISTS total_sales numeric(18,2) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS order_count integer NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS last_order_date date,
    ADD COLUMN IF NOT EXISTS payment_terms varchar(80),
    ADD COLUMN IF NOT EXISTS billing_address text,
    ADD COLUMN IF NOT EXISTS billing_city varchar(120),
    ADD COLUMN IF NOT EXISTS billing_state_region varchar(120),
    ADD COLUMN IF NOT EXISTS billing_postal_code varchar(40),
    ADD COLUMN IF NOT EXISTS billing_country varchar(120),
    ADD COLUMN IF NOT EXISTS primary_contact_id bigint,
    ADD COLUMN IF NOT EXISTS primary_contact_name varchar(180),
    ADD COLUMN IF NOT EXISTS primary_contact_email varchar(180),
    ADD COLUMN IF NOT EXISTS primary_contact_phone varchar(80),
    ADD COLUMN IF NOT EXISTS archived_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS metadata jsonb NOT NULL DEFAULT '{}'::jsonb;

ALTER TABLE crm.leads
    ADD COLUMN IF NOT EXISTS full_name varchar(180),
    ADD COLUMN IF NOT EXISTS source_name varchar(120),
    ADD COLUMN IF NOT EXISTS status_key varchar(80),
    ADD COLUMN IF NOT EXISTS stage_key varchar(80),
    ADD COLUMN IF NOT EXISTS assigned_to_name varchar(180),
    ADD COLUMN IF NOT EXISTS lead_type varchar(40) NOT NULL DEFAULT 'warm',
    ADD COLUMN IF NOT EXISTS score numeric(8,2) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS conversion_rate numeric(8,2) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS approximated_value numeric(18,2) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS currency_code varchar(10) NOT NULL DEFAULT 'GHS',
    ADD COLUMN IF NOT EXISTS expected_close_date date,
    ADD COLUMN IF NOT EXISTS last_activity_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS converted_customer_id bigint,
    ADD COLUMN IF NOT EXISTS lost_reason text,
    ADD COLUMN IF NOT EXISTS risk_rating varchar(40),
    ADD COLUMN IF NOT EXISTS scoring_factors jsonb NOT NULL DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS metadata jsonb NOT NULL DEFAULT '{}'::jsonb;

ALTER TABLE crm.opportunities
    ADD COLUMN IF NOT EXISTS stage_key varchar(80),
    ADD COLUMN IF NOT EXISTS source_name varchar(120),
    ADD COLUMN IF NOT EXISTS owner_name varchar(180),
    ADD COLUMN IF NOT EXISTS currency_code varchar(10) NOT NULL DEFAULT 'GHS',
    ADD COLUMN IF NOT EXISTS actual_close_date date,
    ADD COLUMN IF NOT EXISTS last_activity_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS risk_rating varchar(40),
    ADD COLUMN IF NOT EXISTS metadata jsonb NOT NULL DEFAULT '{}'::jsonb;

UPDATE crm.leads l
SET full_name = NULLIF(trim(concat_ws(' ', l.first_name, l.last_name)), ''),
    source_name = COALESCE(l.source_name, ls.source_name),
    status_key = COALESCE(l.status_key, st.status_code),
    stage_key = COALESCE(l.stage_key, ps.stage_code),
    conversion_rate = CASE
        WHEN l.lead_type = 'hot' THEN 75
        WHEN l.lead_type = 'cold' THEN 25
        ELSE 50
    END,
    approximated_value = COALESCE(l.estimated_value, 0) * (
        CASE
            WHEN l.lead_type = 'hot' THEN 0.75
            WHEN l.lead_type = 'cold' THEN 0.25
            ELSE 0.50
        END
    ),
    last_activity_at = COALESCE(l.last_activity_at, l.updated_at),
    updated_at = CURRENT_TIMESTAMP
FROM crm.lead_sources ls, crm.lead_statuses st, crm.pipeline_stages ps
WHERE l.tenant_id = ls.tenant_id
  AND l.lead_source_id = ls.id
  AND l.tenant_id = st.tenant_id
  AND l.lead_status_id = st.id
  AND l.tenant_id = ps.tenant_id
  AND l.stage_id = ps.id;

UPDATE crm.customers
SET status = CASE WHEN is_active THEN COALESCE(NULLIF(status, ''), 'active') ELSE 'inactive' END,
    credit_status = COALESCE(NULLIF(credit_status, ''), 'good'),
    billing_address = COALESCE(billing_address, notes),
    updated_at = CURRENT_TIMESTAMP
WHERE status IS NULL
   OR credit_status IS NULL;

CREATE TABLE IF NOT EXISTS crm.customer_contacts (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    customer_id bigint NOT NULL REFERENCES crm.customers(id) ON DELETE CASCADE,
    contact_id bigint REFERENCES contacts.contacts(id) ON DELETE SET NULL,
    full_name varchar(180) NOT NULL,
    email varchar(180),
    phone varchar(80),
    role_title varchar(120),
    contact_type varchar(60) NOT NULL DEFAULT 'primary',
    is_primary boolean NOT NULL DEFAULT false,
    is_billing boolean NOT NULL DEFAULT false,
    is_active boolean NOT NULL DEFAULT true,
    is_deleted boolean NOT NULL DEFAULT false,
    deleted_at timestamp without time zone,
    deleted_by uuid,
    created_by uuid,
    updated_by uuid,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS crm.customer_addresses (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    customer_id bigint NOT NULL REFERENCES crm.customers(id) ON DELETE CASCADE,
    address_type varchar(40) NOT NULL DEFAULT 'billing',
    address_label varchar(120),
    address_line1 text NOT NULL,
    address_line2 text,
    city varchar(120),
    state_region varchar(120),
    postal_code varchar(40),
    country varchar(120),
    is_default boolean NOT NULL DEFAULT false,
    is_active boolean NOT NULL DEFAULT true,
    is_deleted boolean NOT NULL DEFAULT false,
    deleted_at timestamp without time zone,
    deleted_by uuid,
    created_by uuid,
    updated_by uuid,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS crm.customer_status_history (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    customer_id bigint NOT NULL REFERENCES crm.customers(id) ON DELETE CASCADE,
    old_status varchar(40),
    new_status varchar(40) NOT NULL,
    reason text,
    changed_by uuid,
    changed_by_name varchar(180),
    changed_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS crm.customer_credit_events (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    customer_id bigint NOT NULL REFERENCES crm.customers(id) ON DELETE CASCADE,
    event_type varchar(60) NOT NULL,
    previous_credit_limit numeric(18,2),
    new_credit_limit numeric(18,2),
    previous_credit_status varchar(40),
    new_credit_status varchar(40),
    previous_credit_used numeric(18,2),
    new_credit_used numeric(18,2),
    payment_terms varchar(80),
    reason text,
    performed_by uuid,
    performed_by_name varchar(180),
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS crm.customer_timeline_events (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    customer_id bigint NOT NULL REFERENCES crm.customers(id) ON DELETE CASCADE,
    event_no varchar(80) NOT NULL,
    event_type varchar(60) NOT NULL,
    title varchar(180) NOT NULL,
    details text,
    reference_module varchar(80),
    reference_type varchar(80),
    reference_id varchar(120),
    amount numeric(18,2),
    currency_code varchar(10) NOT NULL DEFAULT 'GHS',
    status varchar(40),
    occurred_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS crm.crm_activities (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    activity_no varchar(80) NOT NULL,
    entity_type varchar(40) NOT NULL,
    entity_id bigint NOT NULL,
    lead_id bigint REFERENCES crm.leads(id) ON DELETE CASCADE,
    customer_id bigint REFERENCES crm.customers(id) ON DELETE CASCADE,
    opportunity_id bigint REFERENCES crm.opportunities(id) ON DELETE CASCADE,
    activity_type varchar(40) NOT NULL,
    subject varchar(180) NOT NULL,
    description text,
    notes text,
    outcome varchar(180),
    activity_date date,
    occurred_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    duration_minutes integer,
    assigned_to uuid,
    assigned_to_name varchar(180),
    status varchar(40) NOT NULL DEFAULT 'completed',
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    is_deleted boolean NOT NULL DEFAULT false,
    deleted_at timestamp without time zone,
    deleted_by uuid,
    created_by uuid,
    updated_by uuid,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS crm.lead_assignments (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    assignment_no varchar(80) NOT NULL,
    lead_id bigint NOT NULL REFERENCES crm.leads(id) ON DELETE CASCADE,
    from_user_id uuid,
    from_user_name varchar(180),
    to_user_id uuid,
    to_user_name varchar(180) NOT NULL,
    assignment_method varchar(60) NOT NULL DEFAULT 'manual',
    reason text,
    assigned_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    assigned_by uuid,
    assigned_by_name varchar(180),
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE TABLE IF NOT EXISTS crm.lead_assignment_rules (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    rule_code varchar(80) NOT NULL,
    rule_name varchar(180) NOT NULL,
    assignment_method varchar(60) NOT NULL DEFAULT 'round-robin',
    is_enabled boolean NOT NULL DEFAULT false,
    priority integer NOT NULL DEFAULT 100,
    source_filter jsonb NOT NULL DEFAULT '[]'::jsonb,
    stage_filter jsonb NOT NULL DEFAULT '[]'::jsonb,
    sales_reps jsonb NOT NULL DEFAULT '[]'::jsonb,
    max_active_leads integer,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_by uuid,
    updated_by uuid,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS crm.lead_stage_history (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    lead_id bigint NOT NULL REFERENCES crm.leads(id) ON DELETE CASCADE,
    from_stage_id bigint REFERENCES crm.pipeline_stages(id) ON DELETE SET NULL,
    to_stage_id bigint REFERENCES crm.pipeline_stages(id) ON DELETE SET NULL,
    from_stage_key varchar(80),
    to_stage_key varchar(80) NOT NULL,
    reason text,
    changed_by uuid,
    changed_by_name varchar(180),
    changed_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS crm.lead_score_history (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    lead_id bigint NOT NULL REFERENCES crm.leads(id) ON DELETE CASCADE,
    previous_score numeric(8,2),
    new_score numeric(8,2) NOT NULL,
    lead_type varchar(40),
    conversion_rate numeric(8,2),
    approximated_value numeric(18,2),
    scoring_factors jsonb NOT NULL DEFAULT '{}'::jsonb,
    reason text,
    scored_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    scored_by uuid
);

CREATE TABLE IF NOT EXISTS crm.lead_conversion_records (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    lead_id bigint NOT NULL REFERENCES crm.leads(id) ON DELETE CASCADE,
    customer_id bigint REFERENCES crm.customers(id) ON DELETE SET NULL,
    opportunity_id bigint REFERENCES crm.opportunities(id) ON DELETE SET NULL,
    customer_type varchar(40),
    initial_credit_limit numeric(18,2),
    conversion_value numeric(18,2),
    conversion_reason text,
    converted_by uuid,
    converted_by_name varchar(180),
    converted_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE TABLE IF NOT EXISTS crm.crm_analytics_snapshots (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    snapshot_date date NOT NULL,
    period_type varchar(40) NOT NULL DEFAULT 'daily',
    total_leads integer NOT NULL DEFAULT 0,
    active_leads integer NOT NULL DEFAULT 0,
    hot_leads integer NOT NULL DEFAULT 0,
    qualified_leads integer NOT NULL DEFAULT 0,
    converted_leads integer NOT NULL DEFAULT 0,
    won_deals integer NOT NULL DEFAULT 0,
    lost_deals integer NOT NULL DEFAULT 0,
    pipeline_value numeric(18,2) NOT NULL DEFAULT 0,
    won_value numeric(18,2) NOT NULL DEFAULT 0,
    lost_value numeric(18,2) NOT NULL DEFAULT 0,
    conversion_rate numeric(8,2) NOT NULL DEFAULT 0,
    average_deal_time_days numeric(8,2) NOT NULL DEFAULT 0,
    win_loss_ratio numeric(8,2) NOT NULL DEFAULT 0,
    average_score numeric(8,2) NOT NULL DEFAULT 0,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS crm.crm_source_performance (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    period_start date NOT NULL,
    period_end date NOT NULL,
    source_name varchar(120) NOT NULL,
    leads_count integer NOT NULL DEFAULT 0,
    converted_count integer NOT NULL DEFAULT 0,
    conversion_rate numeric(8,2) NOT NULL DEFAULT 0,
    revenue_amount numeric(18,2) NOT NULL DEFAULT 0,
    average_deal_time_days numeric(8,2),
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS crm.crm_stage_dropoff (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    period_start date NOT NULL,
    period_end date NOT NULL,
    from_stage_key varchar(80) NOT NULL,
    to_stage_key varchar(80) NOT NULL,
    transition_label varchar(180) NOT NULL,
    conversion_rate numeric(8,2) NOT NULL DEFAULT 0,
    leads_lost integer NOT NULL DEFAULT 0,
    top_reason varchar(180),
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS crm.crm_ai_insights (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    insight_no varchar(80) NOT NULL,
    insight_type varchar(40) NOT NULL DEFAULT 'insight',
    title varchar(180) NOT NULL,
    description text NOT NULL,
    confidence varchar(40),
    impact_label varchar(120),
    related_metric varchar(120),
    related_entity_type varchar(40),
    related_entity_id bigint,
    recommendation text,
    status varchar(40) NOT NULL DEFAULT 'active',
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at timestamp without time zone
);

CREATE TABLE IF NOT EXISTS crm.crm_risk_items (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    risk_no varchar(80) NOT NULL,
    entity_type varchar(40) NOT NULL,
    entity_id bigint NOT NULL,
    entity_name varchar(180) NOT NULL,
    stage_key varchar(80),
    value_amount numeric(18,2) NOT NULL DEFAULT 0,
    currency_code varchar(10) NOT NULL DEFAULT 'GHS',
    days_silent integer NOT NULL DEFAULT 0,
    risk_level varchar(40) NOT NULL DEFAULT 'medium',
    risk_score numeric(8,2),
    reason text,
    recommended_actions jsonb NOT NULL DEFAULT '[]'::jsonb,
    status varchar(40) NOT NULL DEFAULT 'open',
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    resolved_at timestamp without time zone
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_crm_customer_contacts_primary
    ON crm.customer_contacts (tenant_id, customer_id)
    WHERE is_primary = true AND COALESCE(is_deleted, false) = false;

CREATE UNIQUE INDEX IF NOT EXISTS uq_crm_customer_addresses_default
    ON crm.customer_addresses (tenant_id, customer_id, address_type)
    WHERE is_default = true AND COALESCE(is_deleted, false) = false;

CREATE UNIQUE INDEX IF NOT EXISTS uq_crm_timeline_event_no
    ON crm.customer_timeline_events (tenant_id, event_no);

CREATE UNIQUE INDEX IF NOT EXISTS uq_crm_activity_no
    ON crm.crm_activities (tenant_id, activity_no)
    WHERE COALESCE(is_deleted, false) = false;

CREATE UNIQUE INDEX IF NOT EXISTS uq_crm_lead_assignment_no
    ON crm.lead_assignments (tenant_id, assignment_no);

CREATE UNIQUE INDEX IF NOT EXISTS uq_crm_assignment_rule_code
    ON crm.lead_assignment_rules (tenant_id, rule_code);

CREATE UNIQUE INDEX IF NOT EXISTS uq_crm_analytics_snapshot
    ON crm.crm_analytics_snapshots (tenant_id, snapshot_date, period_type);

CREATE UNIQUE INDEX IF NOT EXISTS uq_crm_source_performance_period
    ON crm.crm_source_performance (tenant_id, period_start, period_end, source_name);

CREATE UNIQUE INDEX IF NOT EXISTS uq_crm_stage_dropoff_period
    ON crm.crm_stage_dropoff (tenant_id, period_start, period_end, from_stage_key, to_stage_key);

CREATE UNIQUE INDEX IF NOT EXISTS uq_crm_ai_insight_no
    ON crm.crm_ai_insights (tenant_id, insight_no);

CREATE UNIQUE INDEX IF NOT EXISTS uq_crm_risk_no
    ON crm.crm_risk_items (tenant_id, risk_no);

CREATE INDEX IF NOT EXISTS idx_crm_customers_status
    ON crm.customers (tenant_id, status, credit_status)
    WHERE COALESCE(is_deleted, false) = false;

CREATE INDEX IF NOT EXISTS idx_crm_leads_stage_assignment
    ON crm.leads (tenant_id, stage_key, assigned_to_name)
    WHERE COALESCE(is_deleted, false) = false;

CREATE INDEX IF NOT EXISTS idx_crm_activities_entity
    ON crm.crm_activities (tenant_id, entity_type, entity_id, occurred_at DESC)
    WHERE COALESCE(is_deleted, false) = false;

CREATE INDEX IF NOT EXISTS idx_crm_stage_history_lead
    ON crm.lead_stage_history (tenant_id, lead_id, changed_at DESC);

CREATE INDEX IF NOT EXISTS idx_crm_customer_timeline
    ON crm.customer_timeline_events (tenant_id, customer_id, occurred_at DESC);

DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'cutehorse_app') THEN
        GRANT USAGE ON SCHEMA crm TO cutehorse_app;
        GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA crm TO cutehorse_app;
        GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA crm TO cutehorse_app;

        ALTER DEFAULT PRIVILEGES IN SCHEMA crm
            GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO cutehorse_app;

        ALTER DEFAULT PRIVILEGES IN SCHEMA crm
            GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app;
    END IF;
END $$;

COMMIT;