-- Create enum types
CREATE TYPE IF NOT EXISTS address_type_enum AS ENUM ('billing_address', 'invoice_address', 'user_address');
CREATE TYPE IF NOT EXISTS tds_applicability_enum AS ENUM ('base_only', 'base_plus_tax');

-- Address table
CREATE TABLE IF NOT EXISTS public.address (
    address_id SERIAL PRIMARY KEY,
    location_id INTEGER,
    addr_1 VARCHAR(255),
    addr_2 VARCHAR(255),
    landmark VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(100),
    country VARCHAR(100),
    pincode VARCHAR(20),
    lat NUMERIC(9,6),
    "long" NUMERIC(9,6),
    type address_type_enum,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(255),
    updated_by VARCHAR(255)
);

-- Program table alterations
ALTER TABLE program_v1
  ADD COLUMN IF NOT EXISTS tds_applicability tds_applicability_enum,
  ADD COLUMN IF NOT EXISTS invoice_sender_name VARCHAR(255),
  ADD COLUMN IF NOT EXISTS invoice_sender_pan VARCHAR(50),
  ADD COLUMN IF NOT EXISTS invoice_sender_cin VARCHAR(50),
  ADD COLUMN IF NOT EXISTS invoice_sender_address_id INTEGER;

ALTER TABLE program_v1
  ADD CONSTRAINT IF NOT EXISTS fk_program_invoice_address FOREIGN KEY (invoice_sender_address_id)
    REFERENCES address(address_id);

-- Program session table alterations
ALTER TABLE program_session
  ADD COLUMN IF NOT EXISTS tds_applicability tds_applicability_enum,
  ADD COLUMN IF NOT EXISTS invoice_sender_name VARCHAR(255),
  ADD COLUMN IF NOT EXISTS invoice_sender_pan VARCHAR(50),
  ADD COLUMN IF NOT EXISTS invoice_sender_cin VARCHAR(50),
  ADD COLUMN IF NOT EXISTS invoice_sender_address_id INTEGER;

ALTER TABLE program_session
  ADD CONSTRAINT IF NOT EXISTS fk_session_invoice_address FOREIGN KEY (invoice_sender_address_id)
    REFERENCES address(address_id);
