-- Company execution budget analysis schema
-- Target database: PostgreSQL 14+

create extension if not exists pgcrypto;

create schema if not exists budget_app;

set search_path = budget_app, public;

create table companies (
    id uuid primary key default gen_random_uuid(),
    code varchar(30) not null unique,
    name varchar(200) not null,
    business_number varchar(30),
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

create table fiscal_years (
    id uuid primary key default gen_random_uuid(),
    company_id uuid not null references companies(id),
    fiscal_year integer not null,
    start_date date not null,
    end_date date not null,
    is_closed boolean not null default false,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    constraint uq_fiscal_year unique (company_id, fiscal_year),
    constraint chk_fiscal_year_dates check (start_date <= end_date)
);

create table departments (
    id uuid primary key default gen_random_uuid(),
    company_id uuid not null references companies(id),
    parent_department_id uuid references departments(id),
    code varchar(30) not null,
    name varchar(100) not null,
    manager_name varchar(100),
    is_active boolean not null default true,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    constraint uq_department_code unique (company_id, code)
);

create table employees (
    id uuid primary key default gen_random_uuid(),
    company_id uuid not null references companies(id),
    department_id uuid references departments(id),
    employee_no varchar(30) not null,
    name varchar(100) not null,
    title varchar(100),
    email varchar(200),
    is_active boolean not null default true,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    constraint uq_employee_no unique (company_id, employee_no)
);

create table business_units (
    id uuid primary key default gen_random_uuid(),
    company_id uuid not null references companies(id),
    code varchar(30) not null,
    name varchar(100) not null,
    is_active boolean not null default true,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    constraint uq_business_unit_code unique (company_id, code)
);

create table vendors (
    id uuid primary key default gen_random_uuid(),
    company_id uuid not null references companies(id),
    code varchar(30) not null,
    name varchar(200) not null,
    business_number varchar(30),
    contact_name varchar(100),
    phone varchar(50),
    email varchar(200),
    is_active boolean not null default true,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    constraint uq_vendor_code unique (company_id, code)
);

create table clients (
    id uuid primary key default gen_random_uuid(),
    company_id uuid not null references companies(id),
    code varchar(30) not null,
    name varchar(200) not null,
    business_number varchar(30),
    contact_name varchar(100),
    phone varchar(50),
    email varchar(200),
    is_active boolean not null default true,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    constraint uq_client_code unique (company_id, code)
);

create table projects (
    id uuid primary key default gen_random_uuid(),
    company_id uuid not null references companies(id),
    business_unit_id uuid references business_units(id),
    department_id uuid references departments(id),
    client_id uuid references clients(id),
    project_code varchar(40) not null,
    project_name varchar(200) not null,
    project_type varchar(50),
    status varchar(30) not null default 'planning',
    contract_amount numeric(18, 2) not null default 0,
    start_date date,
    end_date date,
    manager_employee_id uuid references employees(id),
    description text,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    constraint uq_project_code unique (company_id, project_code),
    constraint chk_project_status check (status in ('planning', 'active', 'on_hold', 'closed', 'cancelled'))
);

create table account_categories (
    id uuid primary key default gen_random_uuid(),
    company_id uuid not null references companies(id),
    code varchar(30) not null,
    name varchar(100) not null,
    category_type varchar(20) not null,
    sort_order integer not null default 0,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    constraint uq_account_category_code unique (company_id, code),
    constraint chk_account_category_type check (category_type in ('revenue', 'cost', 'expense'))
);

create table accounts (
    id uuid primary key default gen_random_uuid(),
    company_id uuid not null references companies(id),
    category_id uuid not null references account_categories(id),
    code varchar(30) not null,
    name varchar(100) not null,
    account_type varchar(20) not null,
    is_active boolean not null default true,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    constraint uq_account_code unique (company_id, code),
    constraint chk_account_type check (account_type in ('revenue', 'cost', 'expense'))
);

create table budget_versions (
    id uuid primary key default gen_random_uuid(),
    company_id uuid not null references companies(id),
    fiscal_year_id uuid not null references fiscal_years(id),
    version_name varchar(100) not null,
    version_no integer not null,
    status varchar(20) not null default 'draft',
    created_by uuid references employees(id),
    approved_by uuid references employees(id),
    approved_at timestamptz,
    notes text,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    constraint uq_budget_version unique (company_id, fiscal_year_id, version_no),
    constraint chk_budget_version_status check (status in ('draft', 'submitted', 'approved', 'archived'))
);

create table budget_items (
    id uuid primary key default gen_random_uuid(),
    company_id uuid not null references companies(id),
    budget_version_id uuid not null references budget_versions(id),
    project_id uuid references projects(id),
    department_id uuid references departments(id),
    account_id uuid not null references accounts(id),
    month_no integer not null,
    planned_amount numeric(18, 2) not null default 0,
    memo text,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    constraint uq_budget_item unique (budget_version_id, project_id, department_id, account_id, month_no),
    constraint chk_budget_month check (month_no between 1 and 12)
);

create table purchase_requests (
    id uuid primary key default gen_random_uuid(),
    company_id uuid not null references companies(id),
    project_id uuid references projects(id),
    department_id uuid references departments(id),
    vendor_id uuid references vendors(id),
    requester_employee_id uuid references employees(id),
    request_no varchar(40) not null,
    request_date date not null,
    status varchar(20) not null default 'requested',
    description text,
    total_amount numeric(18, 2) not null default 0,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    constraint uq_purchase_request_no unique (company_id, request_no),
    constraint chk_purchase_request_status check (status in ('requested', 'approved', 'rejected', 'ordered', 'cancelled'))
);

create table purchase_request_items (
    id uuid primary key default gen_random_uuid(),
    purchase_request_id uuid not null references purchase_requests(id) on delete cascade,
    account_id uuid not null references accounts(id),
    item_name varchar(200) not null,
    quantity numeric(18, 3) not null default 1,
    unit_price numeric(18, 2) not null default 0,
    amount numeric(18, 2) not null default 0,
    needed_date date,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

create table purchase_orders (
    id uuid primary key default gen_random_uuid(),
    company_id uuid not null references companies(id),
    purchase_request_id uuid references purchase_requests(id),
    project_id uuid references projects(id),
    department_id uuid references departments(id),
    vendor_id uuid references vendors(id),
    order_no varchar(40) not null,
    order_date date not null,
    status varchar(20) not null default 'issued',
    total_amount numeric(18, 2) not null default 0,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    constraint uq_purchase_order_no unique (company_id, order_no),
    constraint chk_purchase_order_status check (status in ('issued', 'partial_received', 'received', 'cancelled'))
);

create table purchase_order_items (
    id uuid primary key default gen_random_uuid(),
    purchase_order_id uuid not null references purchase_orders(id) on delete cascade,
    account_id uuid not null references accounts(id),
    item_name varchar(200) not null,
    quantity numeric(18, 3) not null default 1,
    unit_price numeric(18, 2) not null default 0,
    amount numeric(18, 2) not null default 0,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

create table invoices (
    id uuid primary key default gen_random_uuid(),
    company_id uuid not null references companies(id),
    project_id uuid references projects(id),
    department_id uuid references departments(id),
    vendor_id uuid references vendors(id),
    client_id uuid references clients(id),
    invoice_no varchar(40) not null,
    invoice_type varchar(20) not null,
    issue_date date not null,
    due_date date,
    supply_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,
    status varchar(20) not null default 'issued',
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    constraint uq_invoice_no unique (company_id, invoice_no),
    constraint chk_invoice_type check (invoice_type in ('sales', 'purchase')),
    constraint chk_invoice_status check (status in ('issued', 'paid', 'cancelled'))
);

create table actual_transactions (
    id uuid primary key default gen_random_uuid(),
    company_id uuid not null references companies(id),
    fiscal_year_id uuid references fiscal_years(id),
    project_id uuid references projects(id),
    department_id uuid references departments(id),
    account_id uuid not null references accounts(id),
    vendor_id uuid references vendors(id),
    client_id uuid references clients(id),
    employee_id uuid references employees(id),
    source_type varchar(30) not null,
    source_id uuid,
    transaction_date date not null,
    month_no integer not null,
    transaction_type varchar(20) not null,
    amount numeric(18, 2) not null,
    description text,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    constraint chk_actual_month check (month_no between 1 and 12),
    constraint chk_transaction_type check (transaction_type in ('revenue', 'cost', 'expense')),
    constraint chk_source_type check (source_type in ('manual', 'invoice', 'purchase_order', 'erp', 'excel_upload'))
);

create table cashflow_transactions (
    id uuid primary key default gen_random_uuid(),
    company_id uuid not null references companies(id),
    project_id uuid references projects(id),
    department_id uuid references departments(id),
    transaction_date date not null,
    cashflow_type varchar(20) not null,
    amount numeric(18, 2) not null,
    description text,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    constraint chk_cashflow_type check (cashflow_type in ('inflow', 'outflow'))
);

create table file_import_logs (
    id uuid primary key default gen_random_uuid(),
    company_id uuid not null references companies(id),
    import_type varchar(30) not null,
    file_name varchar(255) not null,
    row_count integer not null default 0,
    success_count integer not null default 0,
    failure_count integer not null default 0,
    imported_by uuid references employees(id),
    imported_at timestamptz not null default now(),
    notes text,
    constraint chk_import_type check (import_type in ('budget', 'actual', 'project', 'vendor', 'client', 'account'))
);

create table staging_ptc_transactions (
    id uuid primary key default gen_random_uuid(),
    import_batch varchar(50) not null,
    source_file_name varchar(255) not null,
    source_sheet_name varchar(100) not null default 'Sheet1',
    source_row_no integer not null,
    transaction_date_raw varchar(50),
    transaction_date date,
    in_out varchar(20),
    account_code_raw varchar(30),
    account_name_raw varchar(100),
    department_name_raw varchar(100),
    vendor_name_raw varchar(200),
    project_code_raw varchar(50),
    project_type_raw varchar(50),
    project_name_raw varchar(200),
    description_raw text,
    supply_amount_raw varchar(50),
    vat_amount_raw varchar(50),
    total_amount_raw varchar(50),
    remarks_raw text,
    supply_amount numeric(18, 2),
    vat_amount numeric(18, 2),
    total_amount numeric(18, 2),
    normalized_transaction_type varchar(20),
    load_status varchar(20) not null default 'loaded',
    load_error text,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    constraint uq_staging_ptc_row unique (import_batch, source_row_no),
    constraint chk_staging_load_status check (load_status in ('loaded', 'mapped', 'error'))
);

create index idx_projects_company_status on projects(company_id, status);
create index idx_projects_department on projects(department_id);
create index idx_budget_items_project on budget_items(project_id, month_no);
create index idx_budget_items_department on budget_items(department_id, month_no);
create index idx_actual_transactions_project on actual_transactions(project_id, transaction_date);
create index idx_actual_transactions_department on actual_transactions(department_id, transaction_date);
create index idx_actual_transactions_account on actual_transactions(account_id, transaction_date);
create index idx_purchase_orders_project on purchase_orders(project_id, order_date);
create index idx_invoices_project on invoices(project_id, issue_date);
create index idx_staging_ptc_batch on staging_ptc_transactions(import_batch, source_row_no);
create index idx_staging_ptc_project on staging_ptc_transactions(project_code_raw);
create index idx_staging_ptc_account on staging_ptc_transactions(account_code_raw);
create index idx_staging_ptc_department on staging_ptc_transactions(department_name_raw);

create or replace view vw_budget_vs_actual_monthly as
select
    bv.company_id,
    fy.fiscal_year,
    bi.project_id,
    bi.department_id,
    bi.account_id,
    bi.month_no,
    sum(bi.planned_amount) as budget_amount,
    coalesce(sum(at.amount), 0) as actual_amount,
    sum(bi.planned_amount) - coalesce(sum(at.amount), 0) as variance_amount,
    case
        when sum(bi.planned_amount) = 0 then 0
        else round((coalesce(sum(at.amount), 0) / sum(bi.planned_amount)) * 100, 2)
    end as execution_rate
from budget_items bi
join budget_versions bv on bv.id = bi.budget_version_id
join fiscal_years fy on fy.id = bv.fiscal_year_id
left join actual_transactions at
    on at.company_id = bv.company_id
   and coalesce(at.project_id, '00000000-0000-0000-0000-000000000000'::uuid) = coalesce(bi.project_id, '00000000-0000-0000-0000-000000000000'::uuid)
   and coalesce(at.department_id, '00000000-0000-0000-0000-000000000000'::uuid) = coalesce(bi.department_id, '00000000-0000-0000-0000-000000000000'::uuid)
   and at.account_id = bi.account_id
   and at.month_no = bi.month_no
group by
    bv.company_id,
    fy.fiscal_year,
    bi.project_id,
    bi.department_id,
    bi.account_id,
    bi.month_no;

create or replace view vw_project_profit_summary as
select
    p.id as project_id,
    p.project_code,
    p.project_name,
    p.status,
    p.contract_amount,
    coalesce(sum(case when a.account_type = 'revenue' then at.amount else 0 end), 0) as revenue_amount,
    coalesce(sum(case when a.account_type in ('cost', 'expense') then at.amount else 0 end), 0) as cost_amount,
    coalesce(sum(case when a.account_type = 'revenue' then at.amount else 0 end), 0)
      - coalesce(sum(case when a.account_type in ('cost', 'expense') then at.amount else 0 end), 0) as profit_amount,
    case
        when coalesce(sum(case when a.account_type = 'revenue' then at.amount else 0 end), 0) = 0 then 0
        else round(
            (
                (
                    coalesce(sum(case when a.account_type = 'revenue' then at.amount else 0 end), 0)
                    - coalesce(sum(case when a.account_type in ('cost', 'expense') then at.amount else 0 end), 0)
                )
                / coalesce(sum(case when a.account_type = 'revenue' then at.amount else 0 end), 0)
            ) * 100,
            2
        )
    end as profit_rate
from projects p
left join actual_transactions at on at.project_id = p.id
left join accounts a on a.id = at.account_id
group by p.id, p.project_code, p.project_name, p.status, p.contract_amount;