37 lines
1.6 KiB
MySQL
37 lines
1.6 KiB
MySQL
|
CREATE TABLE IF NOT EXISTS contract_services (
|
||
|
|
id BIGINT PRIMARY KEY,
|
||
|
|
contract_id BIGINT NOT NULL,
|
||
|
|
company_id BIGINT,
|
||
|
|
service_id BIGINT,
|
||
|
|
service_name TEXT,
|
||
|
|
description TEXT,
|
||
|
|
period_type INTEGER,
|
||
|
|
unit_price NUMERIC(15,2),
|
||
|
|
unit_cost NUMERIC(15,2),
|
||
|
|
discount_percent NUMERIC(5,2),
|
||
|
|
adjusted_price NUMERIC(15,2),
|
||
|
|
quantity NUMERIC(10,2),
|
||
|
|
invoice_description TEXT,
|
||
|
|
start_date DATE,
|
||
|
|
end_date DATE,
|
||
|
|
internal_currency_price NUMERIC(15,2),
|
||
|
|
create_date TIMESTAMPTZ,
|
||
|
|
last_modified_date TIMESTAMPTZ,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
synced_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
deleted_at TIMESTAMPTZ
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_contract_services_contract_id ON contract_services(contract_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_contract_services_company_id ON contract_services(company_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_contract_services_service_name ON contract_services(service_name);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_contract_services_is_deleted ON contract_services(is_deleted);
|
||
|
|
|
||
|
|
ALTER TABLE contract_services
|
||
|
|
DROP CONSTRAINT IF EXISTS fk_contract_services_contract;
|
||
|
|
ALTER TABLE contract_services
|
||
|
|
ADD CONSTRAINT fk_contract_services_contract
|
||
|
|
FOREIGN KEY (contract_id) REFERENCES contracts(id) ON DELETE CASCADE;
|