-- imports

CREATE EXTENSION IF NOT EXISTS "pgcrypto";


-- Table: Users
CREATE TABLE IF NOT EXISTS "Users" (
    id integer GENERATED ALWAYS AS IDENTITY NOT NULL,
    "UserName" text,
    "Password" text DEFAULT '12345670'::text,
    "Active" boolean DEFAULT false,
    "Role" text DEFAULT 'NormalUser'::text,
    "HaveMailAccount" boolean DEFAULT false,
    "AllowedApps" jsonb DEFAULT '["Portal", "mbkauthe"]'::jsonb,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    last_login timestamp with time zone,
    "PasswordEnc" character varying(255),
    "FullName" character varying(100),
    "UserId" character varying(9),
    email text DEFAULT 'support@mbktech.org'::text,
    "Image" text DEFAULT 'https://portal.mbktech.org/icon.svg'::text,
    "Bio" text DEFAULT 'I am ....'::text,
    "SocialAccounts" text DEFAULT '{}'::text,
    "Positions" jsonb DEFAULT '{"Not_Permanent": "Member Is Not Permanent"}'::jsonb,
    CONSTRAINT "Users_pkey" PRIMARY KEY (id),
    CONSTRAINT "Users_UserName_key" UNIQUE ("UserName"),
    CONSTRAINT "Users_UserId_key" UNIQUE ("UserId"),
    CONSTRAINT chk_users_userid_length CHECK ("UserId" IS NULL OR length("UserId") = 9)
);
CREATE INDEX IF NOT EXISTS idx_users_active ON "Users" USING btree ("Active");
CREATE INDEX IF NOT EXISTS idx_users_allowedapps_gin ON "Users" USING gin ("AllowedApps");
CREATE INDEX IF NOT EXISTS idx_users_email ON "Users" USING btree (email);
CREATE INDEX IF NOT EXISTS idx_users_last_login ON "Users" USING btree (last_login);
CREATE INDEX IF NOT EXISTS idx_users_positions_gin ON "Users" USING gin ("Positions");
CREATE INDEX IF NOT EXISTS idx_users_role ON "Users" USING btree ("Role");
CREATE INDEX IF NOT EXISTS idx_users_username_cover ON "Users" USING btree ("UserName") INCLUDE ("Active", "Role");
CREATE INDEX IF NOT EXISTS idx_users_userid ON "Users" USING btree ("UserId");
COMMENT ON TABLE "Users" IS '[core]';

-- Table: ApiTokens
CREATE TABLE IF NOT EXISTS "ApiTokens" (
    id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    "UserName" character varying(50) NOT NULL REFERENCES "Users"("UserName") ON DELETE CASCADE,
    "Name" character varying(255) NOT NULL,
    "TokenHash" character varying(128) NOT NULL,
    "Prefix" character varying(32) NOT NULL,
    "Permissions" jsonb DEFAULT '{"scope": "read-only", "allowedApps": null}'::jsonb NOT NULL,
    "LastUsed" timestamp with time zone,
    "CreatedAt" timestamp with time zone DEFAULT now(),
    "ExpiresAt" timestamp with time zone,
    CONSTRAINT "ApiTokens_pkey" PRIMARY KEY (id),
    CONSTRAINT "ApiTokens_TokenHash_key" UNIQUE ("TokenHash"),
    CONSTRAINT chk_apitokens_permissions_scope CHECK ((("Permissions" ->> 'scope'::text) = ANY (ARRAY['read-only'::text, 'write'::text]))),
    CONSTRAINT chk_apitokens_name_not_empty CHECK ((length(TRIM(BOTH FROM "Name")) > 0)),
    CONSTRAINT chk_apitokens_expires_future CHECK ((("ExpiresAt" IS NULL) OR ("ExpiresAt" > "CreatedAt")))
);
CREATE INDEX IF NOT EXISTS idx_apitokens_expires ON "ApiTokens" USING btree ("ExpiresAt") WHERE ("ExpiresAt" IS NOT NULL);
CREATE INDEX IF NOT EXISTS idx_apitokens_permissions_gin ON "ApiTokens" USING gin ("Permissions");
CREATE INDEX IF NOT EXISTS idx_apitokens_permissions_scope ON "ApiTokens" USING btree ((("Permissions" ->> 'scope'::text)));
CREATE INDEX IF NOT EXISTS idx_apitokens_username_created ON "ApiTokens" USING btree ("UserName", "CreatedAt" DESC);
COMMENT ON TABLE "ApiTokens" IS '[core]';

-- Table: PasswordResets
CREATE TABLE IF NOT EXISTS "PasswordResets" (
    id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    "UserName" character varying(50) NOT NULL REFERENCES "Users"("UserName") ON DELETE CASCADE,
    "resetToken" text,
    "resetTokenExpires" timestamp with time zone,
    "resetAttempts" integer DEFAULT 0,
    "lastResetAttempt" timestamp with time zone,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "PasswordResets_pkey" PRIMARY KEY (id)
);
CREATE INDEX IF NOT EXISTS idx_password_resets_token ON "PasswordResets" USING btree ("resetToken");
COMMENT ON TABLE "PasswordResets" IS '[core]';

-- Table: Sessions
CREATE TABLE IF NOT EXISTS "Sessions" (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    "UserName" character varying(50) NOT NULL REFERENCES "Users"("UserName") ON DELETE CASCADE,
    created_at timestamp with time zone DEFAULT now(),
    expires_at timestamp with time zone,
    meta jsonb,
    CONSTRAINT "Sessions_pkey" PRIMARY KEY (id)
);
CREATE INDEX IF NOT EXISTS idx_sessions_expires ON "Sessions" USING btree (expires_at) WHERE (expires_at IS NOT NULL);
CREATE INDEX IF NOT EXISTS idx_sessions_user_created ON "Sessions" USING btree ("UserName", created_at);
CREATE INDEX IF NOT EXISTS idx_sessions_username_expires ON "Sessions" USING btree ("UserName", expires_at);
COMMENT ON TABLE "Sessions" IS '[core]';

-- Table: TrustedDevices
CREATE TABLE IF NOT EXISTS "TrustedDevices" (
    id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    "UserName" character varying(50) NOT NULL REFERENCES "Users"("UserName") ON DELETE CASCADE,
    "DeviceToken" character varying(64) NOT NULL,
    "DeviceName" character varying(255),
    "UserAgent" text,
    "IpAddress" character varying(45),
    "CreatedAt" timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    "ExpiresAt" timestamp with time zone NOT NULL,
    "LastUsed" timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "TrustedDevices_pkey" PRIMARY KEY (id),
    CONSTRAINT "TrustedDevices_DeviceToken_key" UNIQUE ("DeviceToken")
);
CREATE INDEX IF NOT EXISTS idx_trusted_devices_expires ON "TrustedDevices" USING btree ("ExpiresAt");
CREATE INDEX IF NOT EXISTS idx_trusted_devices_username_expires ON "TrustedDevices" USING btree ("UserName", "ExpiresAt");
CREATE INDEX IF NOT EXISTS idx_trusted_devices_token_user_expires ON "TrustedDevices" USING btree ("DeviceToken", "UserName", "ExpiresAt");
COMMENT ON TABLE "TrustedDevices" IS '[core]';

-- Table: TwoFA
CREATE TABLE IF NOT EXISTS "TwoFA" (
    "UserName" text NOT NULL REFERENCES "Users"("UserName") ON DELETE CASCADE,
    "TwoFAStatus" boolean DEFAULT false NOT NULL,
    "TwoFASecret" text,
    CONSTRAINT "TwoFA_pkey" PRIMARY KEY ("UserName")
);
CREATE INDEX IF NOT EXISTS idx_twofa_username_status ON "TwoFA" USING btree ("UserName", "TwoFAStatus");
COMMENT ON TABLE "TwoFA" IS '[core]';

-- Table: ai_history_chatapi
CREATE TABLE IF NOT EXISTS ai_history_chatapi (
    id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    conversation_id uuid DEFAULT uuid_generate_v4() NOT NULL,
    conversation_history jsonb NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    username character varying(100) NOT NULL,
    is_deleted boolean DEFAULT false,
    CONSTRAINT ai_history_chatapi_pkey PRIMARY KEY (id)
);
CREATE INDEX IF NOT EXISTS idx_chat_deleted ON ai_history_chatapi USING btree (is_deleted);
CREATE INDEX IF NOT EXISTS idx_chat_history_gin ON ai_history_chatapi USING gin (conversation_history);
CREATE INDEX IF NOT EXISTS idx_chat_username ON ai_history_chatapi USING btree (username);
COMMENT ON TABLE "ai_history_chatapi" IS '[core]';

-- Table: app_access_requests
CREATE TABLE IF NOT EXISTS app_access_requests (
    id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    username character varying(255) NOT NULL,
    app character varying(255) NOT NULL,
    message text,
    status character varying(20) DEFAULT 'pending'::character varying,
    admin_notes text,
    reviewed_by character varying(255),
    created_at timestamp without time zone DEFAULT now(),
    updated_at timestamp without time zone DEFAULT now(),
    CONSTRAINT app_access_requests_status_check CHECK (((status)::text = ANY ((ARRAY['pending'::character varying, 'approved'::character varying, 'rejected'::character varying])::text[]))),
    CONSTRAINT app_access_requests_pkey PRIMARY KEY (id)
);
COMMENT ON TABLE "app_access_requests" IS '[core]';

-- Table: plan_upgrade_requests
CREATE TABLE IF NOT EXISTS plan_upgrade_requests (
    id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    username character varying(255) NOT NULL REFERENCES "Users"("UserName") ON DELETE CASCADE,
    curr_role character varying(50) NOT NULL,
    requested_plan character varying(50) NOT NULL,
    req_role character varying(50) NOT NULL,
    reason text NOT NULL,
    experience text,
    portfolio character varying(500),
    linkedin character varying(500),
    github character varying(500),
    additional_info text,
    status character varying(20) DEFAULT 'pending'::character varying,
    admin_notes text,
    reviewed_by character varying(255),
    created_at timestamp without time zone DEFAULT now(),
    updated_at timestamp without time zone DEFAULT now(),
    CONSTRAINT plan_upgrade_requests_status_check CHECK (((status)::text = ANY ((ARRAY['pending'::character varying, 'approved'::character varying, 'rejected'::character varying])::text[]))),
    CONSTRAINT plan_upgrade_requests_pkey PRIMARY KEY (id)
);
CREATE INDEX IF NOT EXISTS idx_created_at ON plan_upgrade_requests USING btree (created_at);
CREATE INDEX IF NOT EXISTS idx_req_role_status ON plan_upgrade_requests USING btree (req_role, status);
CREATE INDEX IF NOT EXISTS idx_requested_plan ON plan_upgrade_requests USING btree (requested_plan);
CREATE INDEX IF NOT EXISTS idx_status ON plan_upgrade_requests USING btree (status);
CREATE INDEX IF NOT EXISTS idx_status_created_at ON plan_upgrade_requests USING btree (status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_username_status ON plan_upgrade_requests USING btree (username, status);
COMMENT ON TABLE "plan_upgrade_requests" IS '[core]';

-- Table: session
CREATE TABLE IF NOT EXISTS session (
    sid character varying NOT NULL,
    sess json NOT NULL,
    expire timestamp(6) without time zone NOT NULL,
    username text REFERENCES "Users"("UserName") ON DELETE CASCADE,
    last_activity timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT session_pkey PRIMARY KEY (sid)
);
CREATE INDEX IF NOT EXISTS idx_session_expire ON session USING btree (expire);
CREATE INDEX IF NOT EXISTS idx_session_last_activity ON session USING btree (last_activity);
CREATE INDEX IF NOT EXISTS idx_session_user_id ON session USING btree ((((sess -> 'user'::text) ->> 'id'::text)));
COMMENT ON TABLE "session" IS '[core]';

-- Table: todos
CREATE TABLE IF NOT EXISTS todos (
    id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    username character varying(50) NOT NULL REFERENCES "Users"("UserName"),
    title character varying(255) NOT NULL,
    description text,
    completed boolean DEFAULT false,
    type character varying(20) DEFAULT 'personal'::character varying,
    assigned boolean DEFAULT false,
    assigneduser character varying(50) DEFAULT 'none'::character varying,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT todos_type_check CHECK (((type)::text = ANY ((ARRAY['personal'::character varying, 'admin'::character varying])::text[]))),
    CONSTRAINT todos_pkey PRIMARY KEY (id)
);
CREATE INDEX IF NOT EXISTS idx_todos_assigned ON todos USING btree (assigned);
CREATE INDEX IF NOT EXISTS idx_todos_assigneduser ON todos USING btree (assigneduser);
CREATE INDEX IF NOT EXISTS idx_todos_completed ON todos USING btree (completed);
CREATE INDEX IF NOT EXISTS idx_todos_description ON todos USING btree (description);
CREATE INDEX IF NOT EXISTS idx_todos_title ON todos USING btree (title);
CREATE INDEX IF NOT EXISTS idx_todos_type ON todos USING btree (type);
CREATE INDEX IF NOT EXISTS idx_todos_type_completed ON todos USING btree (type, completed);
CREATE INDEX IF NOT EXISTS idx_todos_username_type ON todos USING btree (username, type);
COMMENT ON TABLE "todos" IS '[core]';

-- Table: user_github
CREATE TABLE IF NOT EXISTS user_github (
    id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    user_name text REFERENCES "Users"("UserName") ON DELETE CASCADE,
    github_id character varying(255),
    github_username character varying(255),
    access_token text,
    created_at timestamp without time zone DEFAULT now(),
    updated_at timestamp without time zone DEFAULT now(),
    installation_id bigint,
    installation_target_type character varying(32),
    CONSTRAINT user_github_pkey PRIMARY KEY (id),
    CONSTRAINT user_github_github_id_key UNIQUE (github_id)
);
CREATE INDEX IF NOT EXISTS idx_user_github_user_name ON user_github USING btree (user_name);
COMMENT ON TABLE "user_github" IS '[core]';

-- Table: user_google
CREATE TABLE IF NOT EXISTS user_google (
    id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    user_name character varying(50) REFERENCES "Users"("UserName"),
    google_id character varying(255),
    google_email character varying(255),
    access_token text,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    CONSTRAINT user_google_pkey PRIMARY KEY (id),
    CONSTRAINT user_google_google_id_key UNIQUE (google_id)
);
COMMENT ON TABLE "user_google" IS '[core]';


-- Table: categories
CREATE TABLE IF NOT EXISTS categories (
    id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    name character varying(255) NOT NULL,
    slug character varying(255) NOT NULL,
    description text,
    image_url character varying(255),
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone,
    CONSTRAINT categories_pkey PRIMARY KEY (id),
    CONSTRAINT categories_name_key UNIQUE (name),
    CONSTRAINT categories_slug_key UNIQUE (slug)
);
COMMENT ON TABLE "categories" IS '[core]';

-- Table: markdowns
CREATE TABLE IF NOT EXISTS markdowns (
    category character varying(255) NOT NULL,
    filename character varying(255) NOT NULL,
    content text,
    CONSTRAINT markdowns_pkey PRIMARY KEY (category, filename)
);
CREATE INDEX IF NOT EXISTS idx_markdowns_category ON markdowns USING btree (category);
CREATE INDEX IF NOT EXISTS idx_markdowns_filename ON markdowns USING btree (filename);
COMMENT ON TABLE "markdowns" IS '[core]';

-- Table: notifications
CREATE TABLE IF NOT EXISTS notifications (
    id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    username character varying(50) NOT NULL,
    type character varying(20) NOT NULL,
    message text NOT NULL,
    is_read boolean DEFAULT false,
    created_at timestamp with time zone DEFAULT (now() AT TIME ZONE 'UTC'::text),
    CONSTRAINT notifications_type_check CHECK (((type)::text = ANY ((ARRAY['info'::character varying, 'warning'::character varying, 'error'::character varying, 'success'::character varying])::text[]))),
    CONSTRAINT notifications_pkey PRIMARY KEY (id)
);
CREATE INDEX IF NOT EXISTS idx_notifications_created_at ON notifications USING btree (created_at);
CREATE INDEX IF NOT EXISTS idx_notifications_username ON notifications USING btree (username);
COMMENT ON TABLE "notifications" IS '[core]';

-- Table: profile_content
CREATE TABLE IF NOT EXISTS profile_content (
    username character varying(255) NOT NULL,
    filename character varying(255) NOT NULL,
    content text,
    CONSTRAINT profile_content_pkey PRIMARY KEY (username, filename)
);
COMMENT ON TABLE "profile_content" IS '[core]';

-- Table: website_access_logs
CREATE TABLE IF NOT EXISTS website_access_logs (
    log_id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    ip_address character varying(50),
    page_url text,
    user_agent text,
    referrer text,
    method character varying(10),
    status_code integer,
    browser_language character varying(255),
    user_id integer,
    action_type character varying(255),
    access_timestamp timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    username character varying(255),
    session_id text,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT website_access_logs_pkey PRIMARY KEY (log_id)
);
CREATE INDEX IF NOT EXISTS idx_website_access_logs_action_type ON website_access_logs USING btree (action_type);
CREATE INDEX IF NOT EXISTS idx_website_access_logs_session_id ON website_access_logs USING btree (session_id);
CREATE INDEX IF NOT EXISTS idx_website_access_logs_timestamp ON website_access_logs USING btree (access_timestamp);
CREATE INDEX IF NOT EXISTS idx_website_access_logs_user_id ON website_access_logs USING btree (user_id);
COMMENT ON TABLE "website_access_logs" IS '[core]';


-- Seed users (hash-only). Default passwords are "12345678" for the sample accounts below.
-- Hashes were generated using mbkauthe's "hashPassword(password, username)" function.
INSERT INTO "Users" ("UserName", "PasswordEnc", "Role", "Active", "HaveMailAccount", "FullName")
VALUES ('support', 'b8b10c1c9006d8c30ab81c412463c65ff6dae3293d9bfbaf5fd8e275081d0947f000a828004e2fbd3a8f6ef5a35ae3eddd4c57b00ecab376b12e607a16a57459', 'SuperAdmin', true, false, 'Support User')
ON CONFLICT ("UserName") DO NOTHING;

-- Migration: add UserId to existing Users tables (idempotent)
ALTER TABLE "Users" ADD COLUMN IF NOT EXISTS "UserId" character varying(9);

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'Users_UserId_key'
    ) THEN
        ALTER TABLE "Users" ADD CONSTRAINT "Users_UserId_key" UNIQUE ("UserId");
    END IF;
END $$;

ALTER TABLE "Users" DROP CONSTRAINT IF EXISTS chk_users_userid_length;
ALTER TABLE "Users" ADD CONSTRAINT chk_users_userid_length CHECK ("UserId" IS NULL OR length("UserId") = 9);

CREATE INDEX IF NOT EXISTS idx_users_userid ON "Users" USING btree ("UserId");