BEGIN;

CREATE SCHEMA IF NOT EXISTS pos;

CREATE TABLE IF NOT EXISTS pos.returns (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    return_no varchar(100) NOT NULL,
    sale_id bigint NOT NULL,
    sale_no varchar(100),
    return_date timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    shop_id bigint,
    shop_name varchar(255),
    cashier_id uuid,
    cashier_name varchar(255),
    customer_id bigint,
    customer_name varchar(255),
    reason text NOT NULL,
    disposition varchar(40) NOT NULL DEFAULT 'restock',
    refund_method varchar(40) NOT NULL DEFAULT 'cash',
    refund_status varchar(40) NOT NULL DEFAULT 'completed',
    status varchar(40) NOT NULL DEFAULT 'completed',
    requires_approval boolean NOT NULL DEFAULT false,
    currency_code varchar(10) NOT NULL DEFAULT 'GHS',
    subtotal numeric(18,2) NOT NULL DEFAULT 0,
    discount_amount numeric(18,2) NOT NULL DEFAULT 0,
    tax_amount numeric(18,2) NOT NULL DEFAULT 0,
    total_amount numeric(18,2) NOT NULL DEFAULT 0,
    accounting_journal_id bigint,
    accounting_journal_no varchar(100),
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    is_deleted boolean NOT NULL DEFAULT false,
    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,
    UNIQUE (tenant_id, return_no)
);

CREATE TABLE IF NOT EXISTS pos.return_items (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    return_id bigint NOT NULL REFERENCES pos.returns(id) ON DELETE CASCADE,
    sale_item_id bigint,
    item_id bigint,
    item_code varchar(100),
    item_name varchar(255) NOT NULL,
    quantity numeric(18,4) NOT NULL,
    unit_price numeric(18,4) NOT NULL DEFAULT 0,
    discount_amount numeric(18,2) NOT NULL DEFAULT 0,
    tax_amount numeric(18,2) NOT NULL DEFAULT 0,
    line_total numeric(18,2) NOT NULL DEFAULT 0,
    unit_of_measure varchar(40) NOT NULL DEFAULT 'EA',
    uom_id bigint,
    warehouse_id bigint,
    warehouse_name varchar(255),
    stock_balance_id bigint,
    disposition varchar(40) NOT NULL DEFAULT 'restock',
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS pos.refunds (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    refund_no varchar(100) NOT NULL,
    return_id bigint NOT NULL REFERENCES pos.returns(id) ON DELETE CASCADE,
    sale_id bigint NOT NULL,
    sale_no varchar(100),
    refund_date timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    refund_method varchar(40) NOT NULL DEFAULT 'cash',
    currency_code varchar(10) NOT NULL DEFAULT 'GHS',
    amount numeric(18,2) NOT NULL DEFAULT 0,
    status varchar(40) NOT NULL DEFAULT 'completed',
    processed_by uuid,
    processed_by_name varchar(255),
    accounting_journal_id bigint,
    accounting_journal_no varchar(100),
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (tenant_id, refund_no)
);

ALTER TABLE pos.returns
    ADD COLUMN IF NOT EXISTS disposition varchar(40) NOT NULL DEFAULT 'restock',
    ADD COLUMN IF NOT EXISTS refund_status varchar(40) NOT NULL DEFAULT 'completed',
    ADD COLUMN IF NOT EXISTS accounting_journal_id bigint,
    ADD COLUMN IF NOT EXISTS accounting_journal_no varchar(100);

ALTER TABLE pos.return_items
    ADD COLUMN IF NOT EXISTS unit_of_measure varchar(40) NOT NULL DEFAULT 'EA',
    ADD COLUMN IF NOT EXISTS uom_id bigint,
    ADD COLUMN IF NOT EXISTS warehouse_id bigint,
    ADD COLUMN IF NOT EXISTS warehouse_name varchar(255),
    ADD COLUMN IF NOT EXISTS stock_balance_id bigint,
    ADD COLUMN IF NOT EXISTS disposition varchar(40) NOT NULL DEFAULT 'restock';

CREATE INDEX IF NOT EXISTS idx_pos_returns_tenant_date ON pos.returns (tenant_id, return_date DESC, id DESC);
CREATE INDEX IF NOT EXISTS idx_pos_returns_sale ON pos.returns (tenant_id, sale_id);
CREATE INDEX IF NOT EXISTS idx_pos_return_items_return ON pos.return_items (tenant_id, return_id);
CREATE INDEX IF NOT EXISTS idx_pos_refunds_return ON pos.refunds (tenant_id, return_id);

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
        ('pos', 'pos.returns.view', 'View POS Returns', 'Allows viewing POS returns and refunds.'),
        ('pos', 'pos.returns.create', 'Create POS Returns', 'Allows creating POS returns from completed sales.'),
        ('pos', 'pos.returns.update', 'Update POS Returns', 'Allows updating POS return status and disposition.'),
        ('pos', 'pos.returns.approve', 'Approve POS Returns', 'Allows approving POS returns that exceed policy thresholds.'),
        ('pos', 'pos.returns.refund', 'Process POS Refunds', 'Allows processing POS refunds.')
) 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
);

INSERT INTO iam.role_permissions (id, role_id, permission_id, created_at)
SELECT gen_random_uuid(), role.id, permission.id, CURRENT_TIMESTAMP
FROM iam.roles role
CROSS JOIN iam.permissions permission
WHERE role.tenant_id IS NOT NULL
  AND role.is_active = TRUE
  AND permission.permission_code IN ('pos.returns.view', 'pos.returns.create', 'pos.returns.update', 'pos.returns.approve', 'pos.returns.refund')
  AND (
      LOWER(role.code) IN ('tenant_owner', 'owner', 'admin', 'administrator', 'pos_user', 'cashier', 'store_manager', 'shop_manager')
      OR LOWER(role.name) IN ('tenant owner', 'owner', 'admin', 'administrator', 'pos user', 'cashier', 'store manager', 'shop manager')
      OR EXISTS (
          SELECT 1
          FROM iam.role_permissions existing_role_permission
          INNER JOIN iam.permissions existing_permission ON existing_permission.id = existing_role_permission.permission_id
          WHERE existing_role_permission.role_id = role.id
            AND (existing_permission.permission_code = 'pos.view' OR existing_permission.permission_code LIKE 'pos.%')
      )
  )
  AND NOT EXISTS (
      SELECT 1 FROM iam.role_permissions existing
      WHERE existing.role_id = role.id AND existing.permission_id = permission.id
  );

COMMIT;