CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE IF NOT EXISTS roles (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), code varchar(80) UNIQUE NOT NULL, name varchar(120) NOT NULL, user_type varchar(20) NOT NULL CHECK(user_type IN ('admin','vendor')), is_system boolean NOT NULL DEFAULT false, status varchar(20) NOT NULL DEFAULT 'active', created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS permissions (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), module_key varchar(120) UNIQUE NOT NULL, module_name varchar(160) NOT NULL, description text, created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS role_permissions (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), role_id uuid NOT NULL REFERENCES roles(id) ON DELETE CASCADE, permission_id uuid NOT NULL REFERENCES permissions(id) ON DELETE CASCADE, can_view boolean NOT NULL DEFAULT false, can_add boolean NOT NULL DEFAULT false, can_edit boolean NOT NULL DEFAULT false, can_delete boolean NOT NULL DEFAULT false, can_all boolean NOT NULL DEFAULT false, UNIQUE(role_id, permission_id));
CREATE TABLE IF NOT EXISTS vendors (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), company_name varchar(255) NOT NULL, contact_person varchar(160), email varchar(255) NOT NULL, phone varchar(40), tax_id varchar(100), category_id uuid, status varchar(30) NOT NULL DEFAULT 'pending', approval_remarks text, bank_details jsonb, address jsonb, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), approved_at timestamptz, approved_by uuid);
CREATE TABLE IF NOT EXISTS users (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), email varchar(255) UNIQUE NOT NULL, password_hash text NOT NULL, display_name varchar(180), user_type varchar(20) NOT NULL CHECK(user_type IN ('admin','vendor')), role_id uuid NOT NULL REFERENCES roles(id), vendor_id uuid REFERENCES vendors(id) ON DELETE SET NULL, status varchar(20) NOT NULL DEFAULT 'active', last_login_at timestamptz, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT vendor_user_scope CHECK((user_type='vendor' AND vendor_id IS NOT NULL) OR (user_type='admin' AND vendor_id IS NULL)));
CREATE INDEX IF NOT EXISTS idx_users_user_type ON users(user_type); CREATE INDEX IF NOT EXISTS idx_users_vendor_id ON users(vendor_id);
CREATE TABLE IF NOT EXISTS refresh_tokens (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE, token_hash text UNIQUE NOT NULL, expires_at timestamptz NOT NULL, revoked_at timestamptz, created_at timestamptz NOT NULL DEFAULT now(), replaced_by uuid);
CREATE TABLE IF NOT EXISTS audit_logs (id bigserial PRIMARY KEY, entity_type varchar(120) NOT NULL, entity_id uuid, action varchar(80) NOT NULL, actor_id uuid, before_state jsonb, after_state jsonb, request_id varchar(120), ip_address inet, user_agent text, created_at timestamptz NOT NULL DEFAULT now());
CREATE INDEX IF NOT EXISTS idx_audit_entity ON audit_logs(entity_type, entity_id); CREATE INDEX IF NOT EXISTS idx_audit_actor_created ON audit_logs(actor_id, created_at DESC);
CREATE TABLE IF NOT EXISTS notifications (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), recipient_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE, event_type varchar(100) NOT NULL, title varchar(255) NOT NULL, message text, payload jsonb, read_at timestamptz, email_sent boolean NOT NULL DEFAULT false, created_at timestamptz NOT NULL DEFAULT now());
CREATE INDEX IF NOT EXISTS idx_notifications_recipient ON notifications(recipient_id, created_at DESC);