CREATE SCHEMA IF NOT EXISTS support;

CREATE TABLE IF NOT EXISTS support.support_tickets (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    ticket_no varchar(80) NOT NULL,
    customer_id bigint REFERENCES crm.customers(id) ON DELETE SET NULL,
    contact_id bigint REFERENCES contacts.contacts(id) ON DELETE SET NULL,
    customer_name varchar(180),
    contact_name varchar(180),
    contact_email varchar(180),
    subject varchar(220) NOT NULL,
    description text,
    category varchar(80),
    channel varchar(60) NOT NULL DEFAULT 'portal',
    priority varchar(40) NOT NULL DEFAULT 'medium',
    status varchar(40) NOT NULL DEFAULT 'open',
    assigned_to uuid,
    assigned_to_name varchar(180),
    first_response_due_at timestamp without time zone,
    resolution_due_at timestamp without time zone,
    first_responded_at timestamp without time zone,
    resolved_at timestamp without time zone,
    closed_at timestamp without time zone,
    escalated_at timestamp without time zone,
    escalation_reason text,
    reopen_count integer NOT NULL DEFAULT 0,
    satisfaction_score integer,
    tags jsonb NOT NULL DEFAULT '[]'::jsonb,
    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
);

ALTER TABLE support.support_tickets
    ADD COLUMN IF NOT EXISTS ticket_no varchar(80),
    ADD COLUMN IF NOT EXISTS customer_id bigint,
    ADD COLUMN IF NOT EXISTS contact_id bigint,
    ADD COLUMN IF NOT EXISTS customer_name varchar(180),
    ADD COLUMN IF NOT EXISTS contact_name varchar(180),
    ADD COLUMN IF NOT EXISTS contact_email varchar(180),
    ADD COLUMN IF NOT EXISTS subject varchar(220),
    ADD COLUMN IF NOT EXISTS description text,
    ADD COLUMN IF NOT EXISTS category varchar(80),
    ADD COLUMN IF NOT EXISTS channel varchar(60) DEFAULT 'portal',
    ADD COLUMN IF NOT EXISTS priority varchar(40) DEFAULT 'medium',
    ADD COLUMN IF NOT EXISTS status varchar(40) DEFAULT 'open',
    ADD COLUMN IF NOT EXISTS assigned_to uuid,
    ADD COLUMN IF NOT EXISTS assigned_to_name varchar(180),
    ADD COLUMN IF NOT EXISTS first_response_due_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS resolution_due_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS first_responded_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS resolved_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS closed_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS escalated_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS escalation_reason text,
    ADD COLUMN IF NOT EXISTS reopen_count integer DEFAULT 0,
    ADD COLUMN IF NOT EXISTS satisfaction_score integer,
    ADD COLUMN IF NOT EXISTS tags jsonb DEFAULT '[]'::jsonb,
    ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS is_deleted boolean DEFAULT false,
    ADD COLUMN IF NOT EXISTS deleted_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS deleted_by uuid,
    ADD COLUMN IF NOT EXISTS created_by uuid,
    ADD COLUMN IF NOT EXISTS updated_by uuid,
    ADD COLUMN IF NOT EXISTS created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP;

UPDATE support.support_tickets
SET
    ticket_no = COALESCE(ticket_no, 'TKT-' || EXTRACT(YEAR FROM CURRENT_TIMESTAMP)::int || '-' || LPAD(id::text, 4, '0')),
    subject = COALESCE(NULLIF(subject, ''), 'Support ticket ' || id::text),
    channel = COALESCE(NULLIF(channel, ''), 'portal'),
    priority = COALESCE(NULLIF(priority, ''), 'medium'),
    status = COALESCE(NULLIF(status, ''), 'open'),
    reopen_count = COALESCE(reopen_count, 0),
    tags = COALESCE(tags, '[]'::jsonb),
    metadata = COALESCE(metadata, '{}'::jsonb),
    is_deleted = COALESCE(is_deleted, false),
    created_at = COALESCE(created_at, CURRENT_TIMESTAMP),
    updated_at = COALESCE(updated_at, CURRENT_TIMESTAMP);

ALTER TABLE support.support_tickets
    ALTER COLUMN ticket_no SET NOT NULL,
    ALTER COLUMN subject SET NOT NULL,
    ALTER COLUMN channel SET NOT NULL,
    ALTER COLUMN priority SET NOT NULL,
    ALTER COLUMN status SET NOT NULL,
    ALTER COLUMN reopen_count SET NOT NULL,
    ALTER COLUMN tags SET NOT NULL,
    ALTER COLUMN metadata SET NOT NULL,
    ALTER COLUMN is_deleted SET NOT NULL,
    ALTER COLUMN created_at SET NOT NULL,
    ALTER COLUMN updated_at SET NOT NULL;

CREATE TABLE IF NOT EXISTS support.ticket_notes (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    ticket_id bigint NOT NULL REFERENCES support.support_tickets(id) ON DELETE CASCADE,
    note_type varchar(40) NOT NULL DEFAULT 'internal',
    body text NOT NULL,
    is_customer_visible boolean NOT NULL DEFAULT false,
    created_by uuid,
    created_by_name varchar(180),
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE support.ticket_notes
    ADD COLUMN IF NOT EXISTS ticket_id bigint,
    ADD COLUMN IF NOT EXISTS note_type varchar(40) DEFAULT 'internal',
    ADD COLUMN IF NOT EXISTS body text,
    ADD COLUMN IF NOT EXISTS is_customer_visible boolean DEFAULT false,
    ADD COLUMN IF NOT EXISTS created_by uuid,
    ADD COLUMN IF NOT EXISTS created_by_name varchar(180),
    ADD COLUMN IF NOT EXISTS created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP;

UPDATE support.ticket_notes
SET
    note_type = COALESCE(NULLIF(note_type, ''), 'internal'),
    body = COALESCE(NULLIF(body, ''), 'Support note'),
    is_customer_visible = COALESCE(is_customer_visible, false),
    created_at = COALESCE(created_at, CURRENT_TIMESTAMP);

ALTER TABLE support.ticket_notes
    ALTER COLUMN note_type SET NOT NULL,
    ALTER COLUMN body SET NOT NULL,
    ALTER COLUMN is_customer_visible SET NOT NULL,
    ALTER COLUMN created_at SET NOT NULL;

CREATE TABLE IF NOT EXISTS support.ticket_status_history (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    ticket_id bigint NOT NULL REFERENCES support.support_tickets(id) ON DELETE CASCADE,
    from_status varchar(40),
    to_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
);

ALTER TABLE support.ticket_status_history
    ADD COLUMN IF NOT EXISTS ticket_id bigint,
    ADD COLUMN IF NOT EXISTS from_status varchar(40),
    ADD COLUMN IF NOT EXISTS to_status varchar(40),
    ADD COLUMN IF NOT EXISTS reason text,
    ADD COLUMN IF NOT EXISTS changed_by uuid,
    ADD COLUMN IF NOT EXISTS changed_by_name varchar(180),
    ADD COLUMN IF NOT EXISTS changed_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP;

UPDATE support.ticket_status_history
SET
    to_status = COALESCE(NULLIF(to_status, ''), 'open'),
    changed_at = COALESCE(changed_at, CURRENT_TIMESTAMP);

ALTER TABLE support.ticket_status_history
    ALTER COLUMN to_status SET NOT NULL,
    ALTER COLUMN changed_at SET NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS uq_support_tickets_ticket_no
    ON support.support_tickets (tenant_id, ticket_no);

CREATE INDEX IF NOT EXISTS idx_support_tickets_customer
    ON support.support_tickets (tenant_id, customer_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_support_tickets_status
    ON support.support_tickets (tenant_id, status, priority, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_support_tickets_assignee
    ON support.support_tickets (tenant_id, assigned_to, status);

CREATE INDEX IF NOT EXISTS idx_support_ticket_notes_ticket
    ON support.ticket_notes (tenant_id, ticket_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_support_ticket_status_history_ticket
    ON support.ticket_status_history (tenant_id, ticket_id, changed_at DESC);

INSERT INTO iam.permissions (id, module_code, permission_code, permission_name, description)
SELECT gen_random_uuid(), seed.module_code, seed.permission_code, seed.permission_name, seed.description
FROM (
    VALUES
        ('helpdesk', 'helpdesk.view', 'View Helpdesk', 'View support tickets and helpdesk dashboards.'),
        ('helpdesk', 'helpdesk.create', 'Create Helpdesk Tickets', 'Create support tickets.'),
        ('helpdesk', 'helpdesk.update', 'Update Helpdesk Tickets', 'Update support tickets, status, priority, and assignment.'),
        ('helpdesk', 'helpdesk.delete', 'Delete Helpdesk Tickets', 'Remove support tickets.'),
        ('helpdesk', 'helpdesk.notes.manage', 'Manage Helpdesk Notes', 'Create and manage internal support notes.')
) AS seed(module_code, permission_code, permission_name, description)
WHERE NOT EXISTS (
    SELECT 1
    FROM iam.permissions permission
    WHERE permission.permission_code = seed.permission_code
);