-- Table: product_group
-- StructuralDiff: Mismatch
-- SOURCE SIGNATURE
COL:id:integer:False:::False|COL:name:character varying:False:255::False|PK:|IDX:btree:unique:id:

-- TARGET SIGNATURE
COL:id:integer:False::nextval('product_group_id_seq'::regclass):False|COL:name:character varying:False:255::False|PK:|IDX:btree:unique:id:

-- SOURCE SCRIPT
CREATE TABLE "product_group" ("id" integer NOT NULL, "name" varchar(255) NOT NULL, PRIMARY KEY ("id"));

-- TARGET SCRIPT
CREATE TABLE "product_group" ("id" integer DEFAULT nextval('product_group_id_seq'::regclass) NOT NULL, "name" varchar(255) NOT NULL, PRIMARY KEY ("id"));

-- Table: schema_versions
-- StructuralDiff: Mismatch
-- SOURCE SIGNATURE
COL:applied_on_utc:timestamp without time zone:False:::False|COL:hash:character varying:True:64::False|COL:script_name:character varying:False:255::False

-- TARGET SIGNATURE
COL:applied_on_utc:timestamp without time zone:False:::False|COL:hash:character varying:True:64::False|COL:script_name:character varying:False:255::False|PK:|IDX:btree:unique:script_name:

-- SOURCE SCRIPT
CREATE TABLE "schema_versions" ("script_name" varchar(255) NOT NULL, "applied_on_utc" timestamp without time zone NOT NULL, "hash" varchar(64));

-- TARGET SCRIPT
CREATE TABLE "schema_versions" ("script_name" varchar(255) NOT NULL, "applied_on_utc" timestamp without time zone NOT NULL, "hash" varchar(64), PRIMARY KEY ("script_name"));

-- Indexes: MissingInSource
-- Optionally drop: DROP INDEX IF EXISTS "PK_schema_versions_id";

-- Table: products
-- StructuralDiff: Mismatch
-- SOURCE SIGNATURE
COL:category_id:uuid:True:::False|COL:created_by:uuid:False:::False|COL:created_on_utc:timestamp without time zone:False:::False|COL:deleted_on_utc:timestamp without time zone:True:::False|COL:description:text:True:::False|COL:hsn_code:text:True:::False|COL:id:uuid:False:::False|COL:is_deleted:boolean:False::false:False|COL:maximum_stock:numeric:False::0.0:False|COL:minimum_stock:numeric:False::0.0:False|COL:modified_by:uuid:True:::False|COL:modified_on_utc:timestamp without time zone:True:::False|COL:opening_stock:numeric:False::0.0:False|COL:product_group_id:integer:False::0:False|COL:product_name:character varying:False:100::False|COL:purchase_price:numeric:False::0.0:False|COL:sac:text:True:::False|COL:selling_price:numeric:False::0.0:False|COL:tax_category_id:integer:False::0:False|COL:unit_id:uuid:True:::False|COL:vendor_id:uuid:True:::False|PK:|FK:modified_by→users.id|FK:product_group_id→product_group.id|FK:tax_category_id→product_tax_categories.id|IDX:btree:unique:id:

-- TARGET SIGNATURE
COL:category_id:uuid:True:::False|COL:created_by:uuid:False:::False|COL:created_on_utc:timestamp without time zone:False:::False|COL:deleted_on_utc:timestamp without time zone:True:::False|COL:description:text:True:::False|COL:hsn_code:text:True:::False|COL:id:uuid:False:::False|COL:is_deleted:boolean:False::false:False|COL:maximum_stock:numeric:False::0.0:False|COL:minimum_stock:numeric:False:::False|COL:modified_by:uuid:True:::False|COL:modified_on_utc:timestamp without time zone:True:::False|COL:opening_stock:numeric:False:::False|COL:product_group_id:integer:False::0:False|COL:product_name:character varying:False:100::False|COL:purchase_price:numeric:False:::False|COL:sac:text:True:::False|COL:selling_price:numeric:False:::False|COL:tax_category_id:integer:False::0:False|COL:unit_id:uuid:True:::False|COL:vendor_id:uuid:True:::False|PK:|FK:modified_by→users.id|FK:product_group_id→product_group.id|FK:tax_category_id→product_tax_categories.id|IDX:btree:unique:id:

-- SOURCE SCRIPT
CREATE TABLE "products" ("id" uuid NOT NULL, "product_name" varchar(100) NOT NULL, "vendor_id" uuid, "unit_id" uuid, "category_id" uuid, "tax_category_id" integer DEFAULT 0 NOT NULL, "product_group_id" integer DEFAULT 0 NOT NULL, "description" text, "hsn_code" text, "sac" text, "purchase_price" numeric(18,2) DEFAULT 0.0 NOT NULL, "selling_price" numeric(18,2) DEFAULT 0.0 NOT NULL, "opening_stock" numeric(18,2) DEFAULT 0.0 NOT NULL, "minimum_stock" numeric(18,2) DEFAULT 0.0 NOT NULL, "maximum_stock" numeric(18,2) DEFAULT 0.0 NOT NULL, "created_on_utc" timestamp without time zone NOT NULL, "modified_on_utc" timestamp without time zone, "deleted_on_utc" timestamp without time zone, "is_deleted" boolean DEFAULT false NOT NULL, "created_by" uuid NOT NULL, "modified_by" uuid, PRIMARY KEY ("id"));

-- TARGET SCRIPT
CREATE TABLE "products" ("id" uuid NOT NULL, "product_name" varchar(100) NOT NULL, "unit_id" uuid, "category_id" uuid, "description" text, "hsn_code" text, "sac" text, "purchase_price" numeric(,) NOT NULL, "selling_price" numeric(,) NOT NULL, "opening_stock" numeric(,) NOT NULL, "minimum_stock" numeric(,) NOT NULL, "vendor_id" uuid, "created_on_utc" timestamp without time zone NOT NULL, "modified_on_utc" timestamp without time zone, "deleted_on_utc" timestamp without time zone, "is_deleted" boolean DEFAULT false NOT NULL, "created_by" uuid NOT NULL, "modified_by" uuid, "tax_category_id" integer DEFAULT 0 NOT NULL, "maximum_stock" numeric(,) DEFAULT 0.0 NOT NULL, "product_group_id" integer DEFAULT 0 NOT NULL, PRIMARY KEY ("id"));

-- Table: gate_pass_logs
Exists in source, missing in target

-- Table: __EFMigrationsHistory
-- StructuralDiff: Mismatch
-- SOURCE SIGNATURE
COL:migration_id:character varying:False:150::False|COL:product_version:character varying:False:32::False

-- TARGET SIGNATURE
COL:migration_id:character varying:False:150::False|COL:product_version:character varying:False:32::False|PK:|IDX:btree:unique:migration_id:

-- SOURCE SCRIPT
CREATE TABLE "__EFMigrationsHistory" ("migration_id" varchar(150) NOT NULL, "product_version" varchar(32) NOT NULL);

-- TARGET SCRIPT
CREATE TABLE "__EFMigrationsHistory" ("migration_id" varchar(150) NOT NULL, "product_version" varchar(32) NOT NULL, PRIMARY KEY ("migration_id"));

-- Indexes: MissingInSource
-- Optionally drop: DROP INDEX IF EXISTS "pk___ef_migrations_history";

-- Table: users
-- StructuralDiff: Mismatch
-- SOURCE SIGNATURE
COL:company_id:uuid:True:::False|COL:created_by:uuid:False:::False|COL:created_on_utc:timestamp without time zone:False:::False|COL:deleted_on_utc:timestamp without time zone:True:::False|COL:email:character varying:False:256::False|COL:first_name:character varying:False:100::False|COL:id:uuid:False:::False|COL:is_deleted:boolean:False::false:False|COL:last_name:character varying:False:100::False|COL:modified_by:uuid:True:::False|COL:modified_on_utc:timestamp without time zone:True:::False|COL:password_hash:text:True:::False|COL:phone_number:character varying:False:15:''::character varying:False|PK:|FK:company_id→companies.id|FK:created_by→users.id|FK:modified_by→users.id|IDX:btree:unique:email,phone_number:|IDX:btree:unique:email:|IDX:btree:unique:id:

-- TARGET SIGNATURE
COL:company_id:uuid:True:::False|COL:created_by:uuid:False:::False|COL:created_on_utc:timestamp without time zone:False:::False|COL:deleted_on_utc:timestamp without time zone:True:::False|COL:email:character varying:False:256::False|COL:first_name:character varying:False:100::False|COL:id:uuid:False:::False|COL:is_deleted:boolean:False::false:False|COL:last_name:character varying:False:100::False|COL:modified_by:uuid:True:::False|COL:modified_on_utc:timestamp without time zone:True:::False|COL:password_hash:text:True:::False|COL:phone_number:character varying:False:15:''::character varying:False|PK:|FK:company_id→companies.id|FK:created_by→users.id|FK:modified_by→users.id|IDX:btree:unique:email,phone_number:|IDX:btree:unique:id:

-- SOURCE SCRIPT
CREATE TABLE "users" ("id" uuid NOT NULL, "first_name" varchar(100) NOT NULL, "last_name" varchar(100) NOT NULL, "email" varchar(256) NOT NULL, "phone_number" varchar(15) DEFAULT ''::character varying NOT NULL, "password_hash" text, "created_on_utc" timestamp without time zone NOT NULL, "modified_on_utc" timestamp without time zone, "deleted_on_utc" timestamp without time zone, "is_deleted" boolean DEFAULT false NOT NULL, "created_by" uuid NOT NULL, "modified_by" uuid, "company_id" uuid, PRIMARY KEY ("id"));

-- TARGET SCRIPT
CREATE TABLE "users" ("id" uuid NOT NULL, "first_name" varchar(100) NOT NULL, "last_name" varchar(100) NOT NULL, "email" varchar(256) NOT NULL, "created_on_utc" timestamp without time zone NOT NULL, "modified_on_utc" timestamp without time zone, "deleted_on_utc" timestamp without time zone, "is_deleted" boolean DEFAULT false NOT NULL, "created_by" uuid NOT NULL, "modified_by" uuid, "password_hash" text, "phone_number" varchar(15) DEFAULT ''::character varying NOT NULL, "company_id" uuid, PRIMARY KEY ("id"));

-- Indexes: MissingInTarget
CREATE UNIQUE INDEX uq_users_email ON public.users USING btree (email)

-- Table: stock_adjustment_headers
Exists in source, missing in target

-- Table: stock_adjustment_details
Exists in source, missing in target

-- Table: stock_adjustment_statuses
Exists in source, missing in target

-- Table: stock_ledgers
Exists in source, missing in target

-- Table: stock_movement_types
Exists in source, missing in target

-- Table: stock_reservation_statuses
Exists in source, missing in target

-- Table: stock_reservations
Exists in source, missing in target

-- Table: stock_transfer_details
Exists in source, missing in target

-- Table: stock_transfer_headers
Exists in source, missing in target

-- Table: stock_transfer_statuses
Exists in source, missing in target

-- Table: user_paid_modules
-- CreateScript: MissingInSource
CREATE TABLE "user_paid_modules" ("user_id" uuid NOT NULL, "role_name" text NOT NULL, PRIMARY KEY ("user_id", "role_name"));

-- PrimaryKeys: MissingInSource
ALTER TABLE "user_paid_modules" ADD CONSTRAINT "pk_user_paid_modules" PRIMARY KEY (user_id, role_name);

-- Columns: MissingInSource
ALTER TABLE "user_paid_modules" ADD COLUMN "user_id" uuid NOT NULL;

-- Columns: MissingInSource
ALTER TABLE "user_paid_modules" ADD COLUMN "role_name" text NOT NULL;

-- ForeignKeys: MissingInSource
ALTER TABLE "user_paid_modules" ADD CONSTRAINT "fk_user_paid_modules_user" FOREIGN KEY (user_id) REFERENCES users(id);

-- Indexes: MissingInSource
CREATE UNIQUE INDEX pk_user_paid_modules ON public.user_paid_modules USING btree (user_id, role_name)

-- Table: user_roles
-- CreateScript: MissingInSource
CREATE TABLE "user_roles" ("user_id" uuid NOT NULL, "paid_modules" bigint NOT NULL, PRIMARY KEY ("user_id"));

-- PrimaryKeys: MissingInSource
ALTER TABLE "user_roles" ADD CONSTRAINT "pk_user_roles" PRIMARY KEY (user_id);

-- Columns: MissingInSource
ALTER TABLE "user_roles" ADD COLUMN "user_id" uuid NOT NULL;

-- Columns: MissingInSource
ALTER TABLE "user_roles" ADD COLUMN "paid_modules" bigint NOT NULL;

-- ForeignKeys: MissingInSource
ALTER TABLE "user_roles" ADD CONSTRAINT "fk_user_roles_user" FOREIGN KEY (user_id) REFERENCES users(id);

-- Indexes: MissingInSource
CREATE UNIQUE INDEX pk_user_roles ON public.user_roles USING btree (user_id)

-- Function: grant_full_schema_access
CREATE OR REPLACE FUNCTION public.grant_full_schema_access(p_schema text, p_user text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
    obj RECORD;
BEGIN
    -- Grant on tables
    FOR obj IN
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = p_schema
          AND table_type = 'BASE TABLE'
    LOOP
        EXECUTE format('GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE %I.%I TO %I;', p_schema, obj.table_name, p_user);
        RAISE NOTICE 'GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE %.% TO %;', p_schema, obj.table_name, p_user;
    END LOOP;

    -- Grant on sequences (USAGE + SELECT + UPDATE: full coverage)
    FOR obj IN
        SELECT c.relname AS sequence_name
        FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relkind = 'S'
          AND n.nspname = p_schema
    LOOP
        EXECUTE format('GRANT USAGE, SELECT, UPDATE ON SEQUENCE %I.%I TO %I;', p_schema, obj.sequence_name, p_user);
        RAISE NOTICE 'GRANT USAGE, SELECT, UPDATE ON SEQUENCE %.% TO %;', p_schema, obj.sequence_name, p_user;
    END LOOP;

    -- Grant on all functions (handles all argument types)
    FOR obj IN
        SELECT
            p.proname AS function_name,
            pg_get_function_identity_arguments(p.oid) AS args
        FROM
            pg_proc p
            JOIN pg_namespace n ON p.pronamespace = n.oid
        WHERE
            n.nspname = p_schema
            AND p.prokind = 'f' -- f = function
    LOOP
        EXECUTE format(
            'GRANT EXECUTE ON FUNCTION %I.%I(%s) TO %I;',
            p_schema, obj.function_name, obj.args, p_user
        );
        RAISE NOTICE 'GRANT EXECUTE ON FUNCTION %.%(%) TO %;', p_schema, obj.function_name, obj.args, p_user;
    END LOOP;

    -- Grant on all procedures (Postgres 11+)
    FOR obj IN
        SELECT
            p.proname AS procedure_name,
            pg_get_function_identity_arguments(p.oid) AS args
        FROM
            pg_proc p
            JOIN pg_namespace n ON p.pronamespace = n.oid
        WHERE
            n.nspname = p_schema
            AND p.prokind = 'p' -- p = procedure
    LOOP
        EXECUTE format(
            'GRANT EXECUTE ON PROCEDURE %I.%I(%s) TO %I;',
            p_schema, obj.procedure_name, obj.args, p_user
        );
        RAISE NOTICE 'GRANT EXECUTE ON PROCEDURE %.%(%) TO %;', p_schema, obj.procedure_name, obj.args, p_user;
    END LOOP;

END;
$function$

-- Function: get_products_by_company_and_group
CREATE OR REPLACE FUNCTION public.get_products_by_company_and_group(in_company_id uuid, in_product_group_id integer)
 RETURNS TABLE(id uuid, product_group_id integer, product_name text, unit_id uuid, unit_name text, category_id uuid, category_name text, description text, hsn_code text, sac text, purchase_price numeric, selling_price numeric, opening_stock numeric, minimum_stock numeric, vendor_id uuid, tax_category_id uuid, maximum_stock numeric, cgst_rate numeric, sgst_rate numeric, igst_rate numeric)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
    SELECT 
        p.id,
        p.product_group_id,
        p.product_name::TEXT,
        p.unit_id,
        u.unit_name::TEXT,
        p.category_id,
        c.category_name::TEXT,
        p.description::TEXT,
        p.hsn_code::TEXT,
        p.sac::TEXT,
        p.purchase_price,
        p.selling_price,
        p.opening_stock,
        p.minimum_stock,
        p.vendor_id,
        p.tax_category_id,
        p.maximum_stock,
        ptcd.c_gst_rate,
        ptcd.s_gst_rate,
        ptcd.i_gst_rate
    FROM products p
    INNER JOIN company_products cp ON p.id = cp.product_id
    LEFT JOIN categories c ON p.category_id = c.id
    LEFT JOIN product_tax_categories ptc ON p.tax_category_id = ptc.id
    LEFT JOIN product_tax_category_details ptcd ON ptc.id = ptcd.producttaxcategory_id
    INNER JOIN product_group pg ON p.product_group_id = pg.id
    LEFT JOIN units u ON u.id = p.unit_id
    WHERE cp.company_id = in_company_id
      AND p.product_group_id = in_product_group_id;
END;
$function$

-- Function: get_all_stock_adjustments_from_span
CREATE OR REPLACE FUNCTION public.get_all_stock_adjustments_from_span(p_company_id uuid, p_user_id uuid, p_warehouse_id uuid DEFAULT NULL::uuid, p_start_date timestamp without time zone DEFAULT NULL::timestamp without time zone, p_end_date timestamp without time zone DEFAULT NULL::timestamp without time zone)
 RETURNS TABLE(id uuid, organization_id uuid, company_id uuid, warehouse_id uuid, stock_adjustment_status text, stock_adjustment_status_id integer, reason_code text, stock_adjustment_number text, remarks text, adjustment_date date, created_by uuid, created_on_utc timestamp without time zone, modified_by uuid, modified_on_utc timestamp without time zone, created_by_name text, modified_by_name text, line_count integer, total_difference numeric)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_start_date date := COALESCE(p_start_date::date, '1970-01-01'::date);
    v_end_date   date := COALESCE(p_end_date::date,   '9999-12-31'::date);
BEGIN
    RETURN QUERY
    WITH headers AS (
        SELECT h.*
        FROM public.stock_adjustment_headers h
        WHERE h.company_id = p_company_id
          AND h.is_deleted = false
          AND (p_warehouse_id IS NULL OR p_warehouse_id = '00000000-0000-0000-0000-000000000000'::uuid OR h.warehouse_id = p_warehouse_id)
          AND (h.adjustment_date_utc::date BETWEEN v_start_date AND v_end_date)
    ), line_agg AS (
        SELECT
            sd.stock_adjustment_header_id,
            COUNT(*)::int AS line_count,
            SUM(sd.difference_quantity)::numeric AS total_difference
        FROM public.stock_adjustment_details sd
        WHERE sd.is_deleted = false
        GROUP BY sd.stock_adjustment_header_id
    )
    SELECT
        h.id,
        h.organization_id,
        h.company_id,
        h.warehouse_id,
        COALESCE(s.name, '') AS stock_adjustment_status,
        h.stock_adjustment_status_id,
        h.reason_code,
        h.stock_adjustment_number,
        h.remarks,
        h.adjustment_date_utc::date AS adjustment_date,
        h.created_by,
        h.created_on_utc,
        h.modified_by,
        h.modified_on_utc,
        CONCAT(cu.first_name, ' ', cu.last_name)::text AS created_by_name,
        CONCAT(mu.first_name, ' ', mu.last_name)::text AS modified_by_name,
        COALESCE(la.line_count, 0) AS line_count,
        COALESCE(la.total_difference, 0.0) AS total_difference
    FROM headers h
    LEFT JOIN public.stock_adjustment_statuses s ON s.id = h.stock_adjustment_status_id
    LEFT JOIN public.users cu ON cu.id = h.created_by
    LEFT JOIN public.users mu ON mu.id = h.modified_by
    LEFT JOIN line_agg la ON la.stock_adjustment_header_id = h.id;
END;
$function$