BEGIN;

CREATE SCHEMA IF NOT EXISTS contacts;

ALTER TABLE contacts.contacts
    ADD COLUMN IF NOT EXISTS organization varchar(180),
    ADD COLUMN IF NOT EXISTS category_key varchar(80),
    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 tags jsonb NOT NULL DEFAULT '[]'::jsonb,
    ADD COLUMN IF NOT EXISTS last_activity_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS preferred_channel varchar(40),
    ADD COLUMN IF NOT EXISTS owner_user_id uuid,
    ADD COLUMN IF NOT EXISTS source varchar(80),
    ADD COLUMN IF NOT EXISTS social_links jsonb NOT NULL DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS converted_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS converted_by uuid,
    ADD COLUMN IF NOT EXISTS converted_customer_id bigint,
    ADD COLUMN IF NOT EXISTS duplicate_key varchar(180),
    ADD COLUMN IF NOT EXISTS metadata jsonb NOT NULL DEFAULT '{}'::jsonb;

UPDATE contacts.contacts
SET organization = COALESCE(organization, company_name),
    status = COALESCE(NULLIF(status, ''), CASE WHEN is_active THEN 'active' ELSE 'inactive' END),
    duplicate_key = COALESCE(duplicate_key, lower(COALESCE(email, '') || '|' || COALESCE(phone, mobile, ''))),
    updated_at = CURRENT_TIMESTAMP
WHERE organization IS NULL
   OR status IS NULL
   OR duplicate_key IS NULL;

ALTER TABLE contacts.contact_addresses
    ADD COLUMN IF NOT EXISTS address_type varchar(40),
    ADD COLUMN IF NOT EXISTS address_label varchar(120),
    ADD COLUMN IF NOT EXISTS country_name varchar(120),
    ADD COLUMN IF NOT EXISTS latitude numeric(10,7),
    ADD COLUMN IF NOT EXISTS longitude numeric(10,7),
    ADD COLUMN IF NOT EXISTS metadata jsonb NOT NULL DEFAULT '{}'::jsonb;

ALTER TABLE contacts.contact_groups
    ADD COLUMN IF NOT EXISTS color varchar(20),
    ADD COLUMN IF NOT EXISTS group_type varchar(40) DEFAULT 'manual',
    ADD COLUMN IF NOT EXISTS metadata jsonb NOT NULL DEFAULT '{}'::jsonb;

CREATE TABLE IF NOT EXISTS contacts.contact_categories (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    category_key varchar(80) NOT NULL,
    category_name varchar(120) NOT NULL,
    description text,
    color varchar(20) NOT NULL DEFAULT '#6b7280',
    display_order integer NOT NULL DEFAULT 0,
    is_system 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 contacts.contact_tags (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    tag_name varchar(80) NOT NULL,
    color varchar(20),
    description text,
    usage_count integer NOT NULL DEFAULT 0,
    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 contacts.contact_tag_assignments (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    contact_id bigint NOT NULL REFERENCES contacts.contacts(id) ON DELETE CASCADE,
    tag_id bigint NOT NULL REFERENCES contacts.contact_tags(id) ON DELETE CASCADE,
    assigned_by uuid,
    assigned_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS contacts.contact_activity_logs (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    activity_no varchar(80) NOT NULL,
    contact_id bigint NOT NULL REFERENCES contacts.contacts(id) ON DELETE CASCADE,
    activity_type varchar(40) NOT NULL,
    description varchar(240) NOT NULL,
    details text,
    user_id uuid,
    user_name varchar(180),
    occurred_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    duration_minutes integer,
    outcome varchar(240),
    related_entity_type varchar(80),
    related_entity_id bigint,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS contacts.contact_import_batches (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    batch_no varchar(80) NOT NULL,
    file_name varchar(180) NOT NULL,
    file_format varchar(20) NOT NULL,
    file_size_bytes bigint,
    import_step varchar(40) NOT NULL DEFAULT 'upload',
    status varchar(40) NOT NULL DEFAULT 'draft',
    field_mappings jsonb NOT NULL DEFAULT '[]'::jsonb,
    total_rows integer NOT NULL DEFAULT 0,
    valid_rows integer NOT NULL DEFAULT 0,
    error_rows integer NOT NULL DEFAULT 0,
    duplicate_rows integer NOT NULL DEFAULT 0,
    imported_rows integer NOT NULL DEFAULT 0,
    progress_percent integer NOT NULL DEFAULT 0,
    uploaded_by uuid,
    started_at timestamp without time zone,
    completed_at timestamp without time zone,
    notes text,
    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 contacts.contact_import_errors (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    import_batch_id bigint NOT NULL REFERENCES contacts.contact_import_batches(id) ON DELETE CASCADE,
    row_number integer NOT NULL,
    field_name varchar(120) NOT NULL,
    field_value text,
    error_message text NOT NULL,
    severity varchar(40) NOT NULL DEFAULT 'error',
    is_resolved boolean NOT NULL DEFAULT false,
    resolved_at timestamp without time zone,
    resolved_by uuid,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS contacts.contact_export_jobs (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    export_no varchar(80) NOT NULL,
    file_format varchar(20) NOT NULL DEFAULT 'csv',
    filters jsonb NOT NULL DEFAULT '{}'::jsonb,
    included_fields jsonb NOT NULL DEFAULT '[]'::jsonb,
    row_count integer NOT NULL DEFAULT 0,
    file_reference text,
    file_name varchar(180),
    status varchar(40) NOT NULL DEFAULT 'queued',
    requested_by uuid,
    requested_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at timestamp without time zone,
    notes text
);

CREATE TABLE IF NOT EXISTS contacts.contact_status_history (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    contact_id bigint NOT NULL REFERENCES contacts.contacts(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 contacts.contact_conversion_records (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    contact_id bigint NOT NULL REFERENCES contacts.contacts(id) ON DELETE CASCADE,
    from_category_key varchar(80),
    to_category_key varchar(80) NOT NULL DEFAULT 'customer',
    from_status varchar(40),
    to_status varchar(40) NOT NULL DEFAULT 'converted',
    customer_id bigint,
    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 contacts.contact_audit_trail (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    contact_id bigint REFERENCES contacts.contacts(id) ON DELETE SET NULL,
    action varchar(80) NOT NULL,
    module_name varchar(80) NOT NULL DEFAULT 'Contacts',
    field_name varchar(120),
    old_value text,
    new_value text,
    performed_by uuid,
    performed_by_name varchar(180),
    role_name varchar(120),
    ip_address inet,
    notes text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_contacts_contact_code_active
    ON contacts.contacts (tenant_id, contact_code)
    WHERE COALESCE(is_deleted, false) = false;

CREATE INDEX IF NOT EXISTS idx_contacts_status
    ON contacts.contacts (tenant_id, status)
    WHERE COALESCE(is_deleted, false) = false;

CREATE INDEX IF NOT EXISTS idx_contacts_category_key
    ON contacts.contacts (tenant_id, category_key)
    WHERE COALESCE(is_deleted, false) = false;

CREATE INDEX IF NOT EXISTS idx_contacts_duplicate_email
    ON contacts.contacts (tenant_id, lower(email))
    WHERE email IS NOT NULL AND COALESCE(is_deleted, false) = false;

CREATE INDEX IF NOT EXISTS idx_contacts_duplicate_phone
    ON contacts.contacts (tenant_id, phone)
    WHERE phone IS NOT NULL AND COALESCE(is_deleted, false) = false;

CREATE INDEX IF NOT EXISTS idx_contacts_tags_gin
    ON contacts.contacts USING gin (tags);

CREATE UNIQUE INDEX IF NOT EXISTS uq_contact_categories_key
    ON contacts.contact_categories (tenant_id, category_key)
    WHERE COALESCE(is_deleted, false) = false;

CREATE UNIQUE INDEX IF NOT EXISTS uq_contact_tags_name
    ON contacts.contact_tags (tenant_id, lower(tag_name))
    WHERE COALESCE(is_deleted, false) = false;

CREATE UNIQUE INDEX IF NOT EXISTS uq_contact_tag_assignments
    ON contacts.contact_tag_assignments (tenant_id, contact_id, tag_id);

CREATE UNIQUE INDEX IF NOT EXISTS uq_contact_activity_no
    ON contacts.contact_activity_logs (tenant_id, activity_no);

CREATE INDEX IF NOT EXISTS idx_contact_activity_contact
    ON contacts.contact_activity_logs (tenant_id, contact_id, occurred_at DESC);

CREATE UNIQUE INDEX IF NOT EXISTS uq_contact_import_batches_no
    ON contacts.contact_import_batches (tenant_id, batch_no);

CREATE INDEX IF NOT EXISTS idx_contact_import_errors_batch
    ON contacts.contact_import_errors (tenant_id, import_batch_id, row_number);

CREATE UNIQUE INDEX IF NOT EXISTS uq_contact_export_jobs_no
    ON contacts.contact_export_jobs (tenant_id, export_no);

CREATE INDEX IF NOT EXISTS idx_contact_status_history_contact
    ON contacts.contact_status_history (tenant_id, contact_id, changed_at DESC);

CREATE INDEX IF NOT EXISTS idx_contact_conversions_contact
    ON contacts.contact_conversion_records (tenant_id, contact_id, converted_at DESC);

CREATE INDEX IF NOT EXISTS idx_contact_audit_trail_contact
    ON contacts.contact_audit_trail (tenant_id, contact_id, created_at DESC);

INSERT INTO contacts.contact_categories (
    tenant_id, category_key, category_name, description, color, display_order, is_system, is_active, is_deleted, created_at, updated_at
)
SELECT t.id, v.category_key, v.category_name, v.description, v.color, v.display_order, TRUE, TRUE, FALSE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
FROM platform.tenants t
CROSS JOIN (VALUES
    ('customer', 'Customer', 'Active paying customers with established business relationships.', '#22c55e', 10),
    ('prospect', 'Prospect', 'Potential customers in the sales pipeline.', '#3b82f6', 20),
    ('vendor', 'Vendor', 'Suppliers and service providers.', '#f59e0b', 30),
    ('partner', 'Partner', 'Strategic business partners and affiliates.', '#8b5cf6', 40),
    ('other', 'Other', 'Contacts that do not fit other categories.', '#6b7280', 50)
) AS v(category_key, category_name, description, color, display_order)
WHERE NOT EXISTS (
    SELECT 1
    FROM contacts.contact_categories c
    WHERE c.tenant_id = t.id
      AND c.category_key = v.category_key
      AND COALESCE(c.is_deleted, false) = false
);

COMMIT;