CREATE TABLE chart_of_accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    outlet_id UUID REFERENCES outlets(id) ON DELETE CASCADE,
    chart_of_account_type_id UUID NOT NULL REFERENCES chart_of_account_types(id) ON DELETE RESTRICT,
    parent_id UUID REFERENCES chart_of_accounts(id) ON DELETE SET NULL,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(20) NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT true,
    is_system BOOLEAN DEFAULT false,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(organization_id, outlet_id, code),
    UNIQUE(organization_id, code) -- For organization-level accounts
);

CREATE INDEX idx_chart_of_accounts_organization_id ON chart_of_accounts(organization_id);
CREATE INDEX idx_chart_of_accounts_outlet_id ON chart_of_accounts(outlet_id);
CREATE INDEX idx_chart_of_accounts_type_id ON chart_of_accounts(chart_of_account_type_id);
CREATE INDEX idx_chart_of_accounts_parent_id ON chart_of_accounts(parent_id);
CREATE INDEX idx_chart_of_accounts_code ON chart_of_accounts(code);
CREATE INDEX idx_chart_of_accounts_is_active ON chart_of_accounts(is_active);
CREATE INDEX idx_chart_of_accounts_is_system ON chart_of_accounts(is_system);