-- Drop existing tables and views
DROP VIEW IF EXISTS change_orders_detail_view;
DROP TABLE IF EXISTS change_order_items CASCADE;
DROP TABLE IF EXISTS change_orders CASCADE;
DROP VIEW IF EXISTS poa_detail_view;
DROP TABLE IF EXISTS poa CASCADE;

-- Create POA table
CREATE TABLE poa (
    id SERIAL PRIMARY KEY,
    contract_no VARCHAR(100) NOT NULL,
    original_contract_no VARCHAR(100) NOT NULL,
    project_id INTEGER NOT NULL,
    vendor_id INTEGER NOT NULL,
    original_contract_name VARCHAR(255) NOT NULL,
    original_status VARCHAR(50) NOT NULL,
    delivery_terms TEXT,
    delivery_date DATE,
    delivery_location VARCHAR(255),
    currency VARCHAR(10),
    total_amount NUMERIC(12,2),
    discount NUMERIC(12,2),
    tax NUMERIC(12,2),
    shipping_fee NUMERIC(12,2),
    net_total NUMERIC(12,2),
    change_reason TEXT,
    approval_status VARCHAR(50) DEFAULT 'PENDING',
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    CONSTRAINT poa_original_contract_no_contracts_contract_no_fk 
        FOREIGN KEY (original_contract_no) 
        REFERENCES contracts(contract_no) 
        ON DELETE CASCADE,
    CONSTRAINT poa_project_id_projects_id_fk 
        FOREIGN KEY (project_id) 
        REFERENCES projects(id) 
        ON DELETE CASCADE,
    CONSTRAINT poa_vendor_id_vendors_id_fk 
        FOREIGN KEY (vendor_id) 
        REFERENCES vendors(id) 
        ON DELETE CASCADE
);

-- Create POA detail view
CREATE VIEW poa_detail_view AS
SELECT 
    -- POA primary information
    poa.id,
    poa.contract_no,
    poa.change_reason,
    poa.approval_status,

    -- Original PO information
    poa.original_contract_no,
    poa.original_contract_name,
    poa.original_status,
    c.start_date as original_start_date,
    c.end_date as original_end_date,

    -- Project information
    poa.project_id,
    p.code as project_code,
    p.name as project_name,

    -- Vendor information
    poa.vendor_id,
    v.vendor_name,

    -- Changed delivery details
    poa.delivery_terms,
    poa.delivery_date,
    poa.delivery_location,

    -- Changed financial information
    poa.currency,
    poa.total_amount,
    poa.discount,
    poa.tax,
    poa.shipping_fee,
    poa.net_total,

    -- Timestamps
    poa.created_at,
    poa.updated_at,
    
    -- Electronic signature status
    EXISTS (
        SELECT 1 
        FROM contract_envelopes 
        WHERE contract_envelopes.contract_id = poa.id
    ) as has_signature
FROM poa
LEFT JOIN contracts c ON poa.original_contract_no = c.contract_no
LEFT JOIN projects p ON poa.project_id = p.id
LEFT JOIN vendors v ON poa.vendor_id = v.id;