diff options
Diffstat (limited to 'db/migrations')
| -rw-r--r-- | db/migrations/0097_poa_initial_setup.sql | 95 |
1 files changed, 95 insertions, 0 deletions
diff --git a/db/migrations/0097_poa_initial_setup.sql b/db/migrations/0097_poa_initial_setup.sql new file mode 100644 index 00000000..fae3f4d1 --- /dev/null +++ b/db/migrations/0097_poa_initial_setup.sql @@ -0,0 +1,95 @@ +-- 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;
\ No newline at end of file |