CREATE TABLE IF NOT EXISTS sales_pipelines (
    id SERIAL PRIMARY KEY,
    pipeline_id VARCHAR(64) NOT NULL UNIQUE,
    tenant_id VARCHAR(64) NOT NULL,
    code VARCHAR(64) NOT NULL,
    name VARCHAR(256) NOT NULL,
    description TEXT,
    is_default BOOLEAN NOT NULL DEFAULT FALSE,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at VARCHAR(64) NOT NULL,
    updated_at VARCHAR(64) NOT NULL,
    CONSTRAINT uq_sales_pipelines_tenant_code UNIQUE (tenant_id, code)
);

CREATE INDEX IF NOT EXISTS ix_sales_pipelines_pipeline_id ON sales_pipelines (pipeline_id);
CREATE INDEX IF NOT EXISTS ix_sales_pipelines_tenant_id ON sales_pipelines (tenant_id);
CREATE INDEX IF NOT EXISTS ix_sales_pipelines_code ON sales_pipelines (code);
CREATE INDEX IF NOT EXISTS idx_sales_pipelines_tenant_default ON sales_pipelines (tenant_id, is_default, is_active);
CREATE UNIQUE INDEX IF NOT EXISTS uq_sales_pipelines_one_default_per_tenant
    ON sales_pipelines (tenant_id)
    WHERE is_default IS TRUE;

CREATE TABLE IF NOT EXISTS sales_pipeline_stages (
    id SERIAL PRIMARY KEY,
    stage_id VARCHAR(64) NOT NULL UNIQUE,
    tenant_id VARCHAR(64) NOT NULL,
    pipeline_id VARCHAR(64) NOT NULL,
    code VARCHAR(64) NOT NULL,
    name VARCHAR(256) NOT NULL,
    category VARCHAR(64) NOT NULL,
    sort_order INTEGER NOT NULL DEFAULT 0,
    is_terminal BOOLEAN NOT NULL DEFAULT FALSE,
    is_system BOOLEAN NOT NULL DEFAULT FALSE,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at VARCHAR(64) NOT NULL,
    updated_at VARCHAR(64) NOT NULL,
    CONSTRAINT uq_sales_pipeline_stages_tenant_pipeline_code UNIQUE (tenant_id, pipeline_id, code)
);

CREATE INDEX IF NOT EXISTS ix_sales_pipeline_stages_stage_id ON sales_pipeline_stages (stage_id);
CREATE INDEX IF NOT EXISTS ix_sales_pipeline_stages_tenant_id ON sales_pipeline_stages (tenant_id);
CREATE INDEX IF NOT EXISTS ix_sales_pipeline_stages_pipeline_id ON sales_pipeline_stages (pipeline_id);
CREATE INDEX IF NOT EXISTS ix_sales_pipeline_stages_code ON sales_pipeline_stages (code);
CREATE INDEX IF NOT EXISTS idx_sales_pipeline_stages_pipeline_order ON sales_pipeline_stages (tenant_id, pipeline_id, sort_order);
CREATE INDEX IF NOT EXISTS idx_sales_pipeline_stages_tenant_active ON sales_pipeline_stages (tenant_id, is_active);