-- OMEGA Ultimate PostgreSQL 16 schema. Mirrors SQLAlchemy Phase 1 models plus roadmap tables.
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE IF NOT EXISTS users (
 id varchar(36) PRIMARY KEY, username varchar(80) NOT NULL UNIQUE, email varchar(254) UNIQUE,
 password_hash varchar(255) NOT NULL, role varchar(24) NOT NULL DEFAULT 'USER', telegram_id varchar(32) UNIQUE,
 is_active boolean NOT NULL DEFAULT true, token_version integer NOT NULL DEFAULT 0, created_at timestamptz NOT NULL DEFAULT now(),
 CONSTRAINT users_role_check CHECK (role IN ('OWNER','SUPERVISOR','MANAGER','AUDITOR','AI_AGENT','PARTNER','USER'))
);
CREATE INDEX IF NOT EXISTS ix_users_role ON users(role);
CREATE TABLE IF NOT EXISTS tickets (
 id varchar(36) PRIMARY KEY, public_id varchar(24) NOT NULL UNIQUE, user_id varchar(36) NOT NULL REFERENCES users(id),
 assignee_id varchar(36) REFERENCES users(id), category varchar(40) NOT NULL DEFAULT 'GENERAL', priority varchar(3) NOT NULL DEFAULT 'P3',
 status varchar(20) NOT NULL DEFAULT 'OPEN', subject varchar(200) NOT NULL, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(),
 CONSTRAINT tickets_priority_check CHECK (priority IN ('P0','P1','P2','P3','P4')),
 CONSTRAINT tickets_status_check CHECK (status IN ('OPEN','IN_PROGRESS','WAITING_USER','ESCALATED','RESOLVED','CLOSED'))
);
CREATE INDEX IF NOT EXISTS ix_tickets_queue ON tickets(status, priority, created_at DESC);
CREATE INDEX IF NOT EXISTS ix_tickets_user ON tickets(user_id);
CREATE INDEX IF NOT EXISTS ix_tickets_assignee ON tickets(assignee_id);
CREATE TABLE IF NOT EXISTS ticket_messages (
 id varchar(36) PRIMARY KEY, ticket_id varchar(36) NOT NULL REFERENCES tickets(id) ON DELETE CASCADE,
 sender_id varchar(36) REFERENCES users(id), sender_role varchar(24) NOT NULL, body text NOT NULL,
 media_type varchar(24), media_ref varchar(512), is_internal boolean NOT NULL DEFAULT false, created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ix_ticket_messages_ticket_time ON ticket_messages(ticket_id, created_at);
CREATE TABLE IF NOT EXISTS audit_logs (
 id varchar(36) PRIMARY KEY, actor_id varchar(36) REFERENCES users(id), action varchar(100) NOT NULL,
 target_type varchar(60) NOT NULL, target_id varchar(64), ip_address varchar(45), metadata jsonb NOT NULL DEFAULT '{}'::jsonb, created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ix_audit_logs_time ON audit_logs(created_at DESC);
CREATE INDEX IF NOT EXISTS ix_audit_logs_actor ON audit_logs(actor_id);
CREATE TABLE IF NOT EXISTS applications (
 id varchar(36) PRIMARY KEY, telegram_id varchar(32) NOT NULL, requested_role varchar(24) NOT NULL,
 category varchar(40) NOT NULL, shift_preference varchar(40) NOT NULL, status varchar(20) NOT NULL DEFAULT 'PENDING',
 document_refs jsonb NOT NULL DEFAULT '[]'::jsonb, reviewer_id varchar(36) REFERENCES users(id), created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ix_applications_status ON applications(status);
CREATE TABLE IF NOT EXISTS roles (
 id bigserial PRIMARY KEY, name varchar(24) NOT NULL UNIQUE, permissions_json jsonb NOT NULL DEFAULT '[]'::jsonb, created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS user_permission_overrides (
 id bigserial PRIMARY KEY, user_id varchar(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 permission varchar(100) NOT NULL, allowed boolean NOT NULL, changed_by varchar(36) REFERENCES users(id), changed_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT uq_user_permission_override UNIQUE(user_id,permission)
);
CREATE TABLE IF NOT EXISTS sessions (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), user_id varchar(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 refresh_token_hash varchar(255), ip_address varchar(45), user_agent text, expires_at timestamptz NOT NULL, revoked_at timestamptz, created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS devices (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), user_id varchar(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 device_label varchar(160), fingerprint_hash varchar(128), last_seen_at timestamptz, trusted boolean NOT NULL DEFAULT false, created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS consent_logs (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), user_id varchar(36) REFERENCES users(id), purpose varchar(100) NOT NULL,
 version varchar(40) NOT NULL, granted boolean NOT NULL, created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS shifts (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), name varchar(80) NOT NULL, timezone varchar(80) NOT NULL DEFAULT 'UTC',
 starts_at time NOT NULL, ends_at time NOT NULL, is_active boolean NOT NULL DEFAULT true, created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS attendance (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), user_id varchar(36) NOT NULL REFERENCES users(id), shift_id uuid REFERENCES shifts(id),
 check_in timestamptz, check_out timestamptz, break_minutes integer NOT NULL DEFAULT 0, status varchar(24) NOT NULL DEFAULT 'OPEN'
);
CREATE TABLE IF NOT EXISTS kpis (
 id bigserial PRIMARY KEY, user_id varchar(36) NOT NULL REFERENCES users(id), period_start timestamptz NOT NULL,
 avg_response_seconds numeric(12,2), resolution_rate numeric(5,2), csat_average numeric(3,2), shift_compliance numeric(5,2), resolved_count integer NOT NULL DEFAULT 0, created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS salary_runs (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), period_start date NOT NULL, period_end date NOT NULL,
 currency char(3) NOT NULL DEFAULT 'INR', status varchar(24) NOT NULL DEFAULT 'DRAFT', created_by varchar(36) REFERENCES users(id), approved_by varchar(36) REFERENCES users(id), created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS salary_slips (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), salary_run_id uuid NOT NULL REFERENCES salary_runs(id), user_id varchar(36) NOT NULL REFERENCES users(id),
 base_amount numeric(14,2) NOT NULL DEFAULT 0, bonus_amount numeric(14,2) NOT NULL DEFAULT 0, deductions numeric(14,2) NOT NULL DEFAULT 0,
 net_amount numeric(14,2) NOT NULL DEFAULT 0, currency char(3) NOT NULL DEFAULT 'INR', details jsonb NOT NULL DEFAULT '{}'::jsonb
);
CREATE TABLE IF NOT EXISTS payouts (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), salary_slip_id uuid REFERENCES salary_slips(id), provider varchar(60), provider_reference varchar(200),
 amount numeric(14,2) NOT NULL, currency char(3) NOT NULL, status varchar(24) NOT NULL DEFAULT 'PENDING', created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS threat_events (
 id bigserial PRIMARY KEY, severity varchar(12) NOT NULL, source_ip varchar(45), actor_id varchar(36) REFERENCES users(id), event_type varchar(80) NOT NULL,
 summary varchar(500) NOT NULL, details jsonb NOT NULL DEFAULT '{}'::jsonb, resolved_at timestamptz, created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS blocked_entities (
 id bigserial PRIMARY KEY, entity_type varchar(24) NOT NULL, entity_value_hash varchar(128) NOT NULL, reason varchar(500), expires_at timestamptz, created_by varchar(36) REFERENCES users(id), created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS ai_suggestions (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), ticket_id varchar(36) REFERENCES tickets(id), model varchar(120), confidence numeric(5,4),
 suggestion text NOT NULL, status varchar(24) NOT NULL DEFAULT 'PENDING_REVIEW', reviewed_by varchar(36) REFERENCES users(id), created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS broadcasts (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), title varchar(200) NOT NULL, body text NOT NULL, audience jsonb NOT NULL DEFAULT '{}'::jsonb,
 status varchar(24) NOT NULL DEFAULT 'DRAFT', created_by varchar(36) REFERENCES users(id), scheduled_at timestamptz, created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS notifications (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), user_id varchar(36) REFERENCES users(id), channel varchar(24) NOT NULL,
 title varchar(200) NOT NULL, body text NOT NULL, status varchar(24) NOT NULL DEFAULT 'QUEUED', sent_at timestamptz, created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS feature_flags (
 key varchar(100) PRIMARY KEY, enabled boolean NOT NULL DEFAULT false, config jsonb NOT NULL DEFAULT '{}'::jsonb, updated_by varchar(36) REFERENCES users(id), updated_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO roles(name,permissions_json) VALUES
 ('OWNER','["*"]'::jsonb),
 ('SUPERVISOR','["users.view","tickets.view","tickets.reply","tickets.assign","applications.view","audit.view","analytics.view","shifts.view"]'::jsonb),
 ('MANAGER','["tickets.view","tickets.reply","tickets.assign","shifts.view"]'::jsonb),
 ('AUDITOR','["users.view","tickets.view","audit.view","analytics.view","applications.view"]'::jsonb),
 ('AI_AGENT','["tickets.view"]'::jsonb),
 ('PARTNER','["tickets.view"]'::jsonb),
 ('USER','["tickets.create","tickets.view","tickets.reply"]'::jsonb)
ON CONFLICT(name) DO UPDATE SET permissions_json = EXCLUDED.permissions_json;

CREATE TABLE IF NOT EXISTS authorized_telegram_ids (
 id varchar(36) PRIMARY KEY, telegram_id varchar(32) NOT NULL UNIQUE, requested_role varchar(24) NOT NULL DEFAULT 'MANAGER',
 enabled boolean NOT NULL DEFAULT true, note varchar(500), authorized_by varchar(36) REFERENCES users(id), created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ix_authorized_telegram_ids_telegram ON authorized_telegram_ids(telegram_id);

ALTER TABLE users ADD COLUMN IF NOT EXISTS token_version integer NOT NULL DEFAULT 0;