create table public.customers
(
    customer_id   bigserial
        primary key,
    customer_type text                                not null
        constraint chk_customers_type_value
            check (customer_type = ANY (ARRAY ['individual'::text, 'business'::text])),
    first_name    text,
    last_name     text,
    company_name  text,
    email         text
        unique,
    phone         text,
    date_of_birth date,
    status        text      default 'active'::text    not null
        constraint check_status
            check (status = ANY (ARRAY ['active'::text, 'suspended'::text, 'closed'::text])),
    created_at    timestamp default CURRENT_TIMESTAMP not null,
    updated_at    timestamp default CURRENT_TIMESTAMP not null,
    constraint chk_customers_type_fields
        check (((customer_type = 'individual'::text) AND (first_name IS NOT NULL) AND (last_name IS NOT NULL)) OR
               ((customer_type = 'business'::text) AND (company_name IS NOT NULL)))
);

alter table public.customers
    owner to postgres;

create table public.customer_addresses
(
    address_id   bigserial
        primary key,
    customer_id  bigint                              not null
        constraint fk_customer_addresses_customer
            references public.customers,
    address_type text                                not null
        constraint chk_customer_addresses_type
            check (address_type = ANY
                   (ARRAY ['billing'::text, 'service'::text, 'home'::text, 'office'::text, 'other'::text])),
    country      text                                not null,
    city         text                                not null,
    street       text                                not null,
    postal_code  text,
    latitude     numeric,
    longitude    numeric,
    is_primary   boolean   default false             not null,
    created_at   timestamp default CURRENT_TIMESTAMP not null
);

alter table public.customer_addresses
    owner to postgres;

create table public.billing_cycles
(
    billing_cycle_id bigserial
        primary key,
    cycle_name       text                                not null
        unique,
    day_of_month     integer                             not null
        constraint chk_billing_cycles_day
            check ((day_of_month >= 1) AND (day_of_month <= 31)),
    grace_days       integer   default 0                 not null
        constraint chk_billing_cycles_grace
            check (grace_days >= 0),
    is_active        boolean   default true              not null,
    created_at       timestamp default CURRENT_TIMESTAMP not null
);

alter table public.billing_cycles
    owner to postgres;

create table public.accounts
(
    account_id       bigserial
        primary key,
    customer_id      bigint                              not null
        constraint fk_accounts_customer
            references public.customers,
    account_number   text                                not null
        unique,
    account_status   text      default 'active'::text    not null
        constraint check_status
            check (account_status = ANY (ARRAY ['active'::text, 'suspended'::text, 'closed'::text])),
    credit_limit     numeric   default 0.00              not null,
    current_balance  numeric   default 0.00              not null
        constraint balance_above_zero
            check (current_balance >= (0)::numeric),
    billing_cycle_id bigint
        constraint fk_accounts_billing_cycle
            references public.billing_cycles,
    created_at       timestamp default CURRENT_TIMESTAMP not null,
    updated_at       timestamp default CURRENT_TIMESTAMP not null
);

alter table public.accounts
    owner to postgres;

create table public.contracts
(
    contract_id     bigserial
        primary key,
    customer_id     bigint                         not null
        constraint fk_contracts_customer
            references public.customers,
    account_id      bigint                         not null
        constraint fk_contracts_account
            references public.accounts,
    contract_type   text                           not null,
    contract_number text                           not null
        unique,
    start_date      date                           not null,
    end_date        date,
    auto_renew      boolean default false          not null,
    status          text    default 'active'::text not null,
    signed_at       timestamp,
    constraint chk_contracts_dates
        check ((end_date IS NULL) OR (end_date >= start_date))
);

alter table public.contracts
    owner to postgres;

create table public.departments
(
    department_id   bigserial
        primary key,
    department_name text                        not null
        unique,
    location        text,
    status          text default 'active'::text not null
);

alter table public.departments
    owner to postgres;

create table public.employee_roles
(
    role_id          bigserial
        primary key,
    role_name        text                 not null
        unique,
    role_description text                 not null,
    access_level     integer default 1    not null
        constraint chk_employee_roles_access
            check (access_level >= 1),
    is_active        boolean default true not null
);

alter table public.employee_roles
    owner to postgres;

create table public.employees
(
    employee_id       bigserial
        primary key,
    department_id     bigint                      not null
        constraint fk_employees_department
            references public.departments,
    role_id           bigint                      not null
        constraint fk_employees_role
            references public.employee_roles,
    first_name        text                        not null,
    last_name         text                        not null,
    email             text                        not null
        unique,
    phone             text,
    hire_date         date                        not null,
    employment_status text default 'active'::text not null,
    manager_id        bigint
        constraint fk_employees_manager
            references public.employees
);

alter table public.employees
    owner to postgres;

create table public.devices
(
    device_id     bigserial
        primary key,
    imei          text
        unique,
    serial_number text
        unique,
    manufacturer  text,
    model         text,
    device_type   text
        constraint check_device_type
            check (device_type = ANY (ARRAY ['smartphone'::text, 'tablet'::text, 'modem'::text, 'iot_device'::text])),
    purchase_date date
);

alter table public.devices
    owner to postgres;

create table public.sim_cards
(
    sim_id    bigserial
        primary key,
    iccid     text                           not null
        unique,
    imsi      text                           not null
        unique,
    msisdn    text                           not null
        unique,
    sim_type  text                           not null
        constraint check_type
            check (sim_type = ANY (ARRAY ['nano'::text, 'micro'::text, 'eSIM'::text])),
    pin_code  text,
    puk_code  text,
    status    text default 'available'::text not null
        constraint check_name
            check (status = ANY (ARRAY ['available'::text, 'active'::text, 'suspended'::text, 'deactivated'::text])),
    issued_at timestamp
);

alter table public.sim_cards
    owner to postgres;

create table public.services
(
    service_id       bigserial
        primary key,
    service_name     text                                not null
        unique,
    service_category text                                not null,
    description      text,
    is_active        boolean   default true              not null,
    created_at       timestamp default CURRENT_TIMESTAMP not null
);

alter table public.services
    owner to postgres;

create table public.products
(
    product_id   bigserial
        primary key,
    product_name text                                not null,
    product_code text                                not null
        unique,
    product_type text                                not null,
    description  text,
    status       text      default 'active'::text    not null,
    created_at   timestamp default CURRENT_TIMESTAMP not null
);

alter table public.products
    owner to postgres;

create table public.overage_policies
(
    overage_policy_id    bigserial
        primary key,
    policy_name          text                           not null
        unique,
    voice_rate_per_min   numeric default 0.0000         not null,
    sms_rate             numeric default 0.0000         not null,
    data_rate_per_mb     numeric default 0.0000         not null,
    throttle_after_limit boolean default false          not null,
    fair_use_limit_mb    numeric,
    status               text    default 'active'::text not null
);

alter table public.overage_policies
    owner to postgres;

create table public.plans
(
    plan_id              bigserial
        primary key,
    product_id           bigint                         not null
        constraint fk_plans_product
            references public.products,
    plan_name            text                           not null,
    monthly_fee          numeric default 0.00           not null
        constraint check_monthly_fee
            check (monthly_fee >= (0)::numeric),
    overage_policy_id    bigint
        constraint fk_plans_overage_policy
            references public.overage_policies,
    contract_term_months integer default 0              not null
        constraint chk_plans_term
            check (contract_term_months >= 0),
    status               text    default 'active'::text not null
);

alter table public.plans
    owner to postgres;

create table public.subscriptions
(
    subscription_id     bigserial
        primary key,
    account_id          bigint                      not null
        constraint fk_subscriptions_account
            references public.accounts,
    plan_id             bigint                      not null
        constraint fk_subscriptions_plan
            references public.plans,
    contract_id         bigint
        constraint fk_subscriptions_contract
            references public.contracts,
    subscription_number text                        not null
        unique,
    activation_date     date                        not null,
    end_date            date,
    status              text default 'active'::text not null
        constraint check_status
            check (status = ANY
                   (ARRAY ['active'::text, 'suspended'::text, 'cancelled'::text, 'expired'::text, 'pending'::text])),
    billing_start_date  date                        not null,
    constraint chk_subscriptions_dates
        check ((end_date IS NULL) OR (end_date >= activation_date))
);

alter table public.subscriptions
    owner to postgres;

create table public.addons
(
    addon_id        bigserial
        primary key,
    addon_name      text                           not null,
    addon_type      text                           not null,
    price           numeric default 0.00           not null,
    allowance_value numeric,
    allowance_unit  text,
    is_recurring    boolean default true           not null,
    status          text    default 'active'::text not null
);

alter table public.addons
    owner to postgres;

create table public.subscription_addons
(
    subscription_addon_id bigserial
        primary key,
    subscription_id       bigint                         not null
        constraint fk_subscription_addons_subscription
            references public.subscriptions,
    addon_id              bigint                         not null
        constraint fk_subscription_addons_addon
            references public.addons,
    activation_date       date                           not null,
    deactivation_date     date,
    status                text    default 'active'::text not null
        constraint check_addon_status
            check (status = ANY (ARRAY ['active'::text, 'deactivated'::text])),
    price_at_activation   numeric default 0.00           not null,
    constraint chk_subscription_addons_dates
        check ((deactivation_date IS NULL) OR (deactivation_date >= activation_date))
);

alter table public.subscription_addons
    owner to postgres;

create table public.subscription_status_history
(
    status_history_id      bigserial
        primary key,
    subscription_id        bigint                              not null
        constraint fk_sub_status_history_subscription
            references public.subscriptions,
    old_status             text,
    new_status             text                                not null,
    changed_at             timestamp default CURRENT_TIMESTAMP not null,
    changed_by_employee_id bigint
        constraint fk_sub_status_history_employee
            references public.employees,
    reason                 text
);

alter table public.subscription_status_history
    owner to postgres;

create index idx_subs_id_subs_stat_hist
    on public.subscription_status_history (subscription_id);

create table public.device_assignments
(
    device_assignment_id bigserial
        primary key,
    device_id            bigint                      not null
        constraint fk_device_assignments_device
            references public.devices,
    subscription_id      bigint                      not null
        constraint fk_device_assignments_subscription
            references public.subscriptions,
    assigned_from        timestamp                   not null,
    assigned_to          timestamp,
    assignment_status    text default 'active'::text not null
        constraint check_assignment_status
            check (assignment_status = ANY (ARRAY ['active'::text, 'returned'::text, 'lost'::text, 'inactive'::text])),
    notes                text,
    constraint chk_device_assignments_times
        check ((assigned_to IS NULL) OR (assigned_to >= assigned_from))
);

alter table public.device_assignments
    owner to postgres;

create index idx_device_assignments_subscription_id
    on public.device_assignments (subscription_id);

create table public.network_technologies
(
    technology_id   bigserial
        primary key,
    technology_name text                        not null
        unique,
    generation      text                        not null,
    description     text,
    status          text default 'active'::text not null
);

alter table public.network_technologies
    owner to postgres;

create table public.network_sites
(
    site_id   bigserial
        primary key,
    site_code text                        not null
        unique,
    site_name text                        not null,
    address   text,
    region    text,
    latitude  numeric,
    longitude numeric,
    site_type text,
    status    text default 'active'::text not null,
    opened_at timestamp
);

alter table public.network_sites
    owner to postgres;

create table public.cell_towers
(
    tower_id          bigserial
        primary key,
    site_id           bigint                      not null
        constraint fk_cell_towers_site
            references public.network_sites,
    tower_code        text                        not null
        unique,
    height_meters     numeric,
    ownership_type    text,
    installation_date date,
    status            text default 'active'::text not null,
    vendor_name       text
);

alter table public.cell_towers
    owner to postgres;

create table public.tower_sectors
(
    sector_id      bigserial
        primary key,
    tower_id       bigint                      not null
        constraint fk_tower_sectors_tower
            references public.cell_towers,
    sector_label   text                        not null,
    azimuth        integer,
    beamwidth      integer
        constraint chk_tower_sectors_beamwidth
            check ((beamwidth >= 1) AND (beamwidth <= 360)),
    frequency_band text,
    technology_id  bigint                      not null
        constraint fk_tower_sectors_technology
            references public.network_technologies,
    status         text default 'active'::text not null,
    constraint uq_tower_sector_label
        unique (tower_id, sector_label)
);

alter table public.tower_sectors
    owner to postgres;

create table public.coverage_zones
(
    coverage_zone_id     bigserial
        primary key,
    sector_id            bigint not null
        constraint fk_coverage_zones_sector
            references public.tower_sectors,
    coverage_type        text   not null,
    zone_description     text,
    signal_quality_score numeric
        constraint chk_signal_quality_score
            check ((signal_quality_score IS NULL) OR
                   ((signal_quality_score >= (0)::numeric) AND (signal_quality_score <= (100)::numeric))),
    last_measured_at     timestamp
);

alter table public.coverage_zones
    owner to postgres;

create table public.roaming_partners
(
    roaming_partner_id bigserial
        primary key,
    partner_name       text                        not null,
    country            text                        not null,
    mcc                text,
    mnc                text,
    agreement_start    date,
    agreement_end      date,
    status             text default 'active'::text not null,
    constraint chk_roaming_partners_dates
        check ((agreement_end IS NULL) OR (agreement_end >= agreement_start))
);

alter table public.roaming_partners
    owner to postgres;

create table public.network_alarms
(
    alarm_id    bigserial
        primary key,
    tower_id    bigint
        constraint fk_network_alarms_tower
            references public.cell_towers,
    sector_id   bigint
        constraint fk_network_alarms_sector
            references public.tower_sectors,
    alarm_type  text                      not null
        constraint chk_network_alarms_type
            check (alarm_type = ANY
                   (ARRAY ['antenna_misalignment'::text, 'signal_degradation'::text, 'battery_low'::text, 'software_error'::text, 'high_temperature'::text, 'hardware_fault'::text, 'link_down'::text, 'backhaul_congestion'::text, 'power_failure'::text, 'high_cpu'::text])),
    severity    text                      not null
        constraint chk_network_alarms_severity
            check (severity = ANY (ARRAY ['warning'::text, 'minor'::text, 'major'::text, 'critical'::text])),
    raised_at   timestamp                 not null,
    cleared_at  timestamp,
    status      text default 'open'::text not null
        constraint chk_network_alarms_status
            check (status = ANY (ARRAY ['open'::text, 'cleared'::text])),
    description text,
    constraint chk_network_alarms_times
        check ((cleared_at IS NULL) OR (cleared_at >= raised_at)),
    constraint chk_alarm_exactly_one_location
        check (((tower_id IS NOT NULL) AND (sector_id IS NULL)) OR ((tower_id IS NULL) AND (sector_id IS NOT NULL)))
);

alter table public.network_alarms
    owner to postgres;

create table public.outages
(
    outage_id   bigserial
        primary key,
    site_id     bigint                    not null
        constraint fk_outages_site
            references public.network_sites,
    alarm_id    bigint
        constraint fk_outages_alarm
            references public.network_alarms,
    outage_type text                      not null,
    start_time  timestamp                 not null,
    end_time    timestamp,
    status      text default 'open'::text not null,
    root_cause  text,
    constraint chk_outages_times
        check ((end_time IS NULL) OR (end_time >= start_time))
);

alter table public.outages
    owner to postgres;

create table public.usage_cdr_calls
(
    call_cdr_id        bigserial
        primary key,
    subscription_id    bigint                 not null
        constraint fk_cdr_calls_subscription
            references public.subscriptions,
    originating_msisdn text                   not null,
    destination_msisdn text                   not null,
    sector_id          bigint
        constraint fk_cdr_calls_sector
            references public.tower_sectors,
    roaming_partner_id bigint
        constraint fk_cdr_calls_roaming_partner
            references public.roaming_partners,
    event_start_time   timestamp              not null,
    event_end_time     timestamp              not null,
    duration_seconds   integer                not null
        constraint chk_cdr_calls_duration
            check (duration_seconds >= 0),
    call_type          text                   not null,
    direction          text                   not null
        constraint chk_cdr_calls_direction
            check (direction = ANY (ARRAY ['outbound'::text, 'inbound'::text])),
    charge_amount      numeric default 0.0000 not null,
    fraud_score        numeric default 0.00,
    constraint chk_cdr_calls_times
        check (event_end_time >= event_start_time)
);

alter table public.usage_cdr_calls
    owner to postgres;

create index idx_subs_id_cdr_calls
    on public.usage_cdr_calls (subscription_id);

create table public.usage_cdr_sms
(
    sms_cdr_id         bigserial
        primary key,
    subscription_id    bigint                 not null
        constraint fk_cdr_sms_subscription
            references public.subscriptions,
    source_msisdn      text                   not null,
    destination_msisdn text                   not null,
    sector_id          bigint
        constraint fk_cdr_sms_sector
            references public.tower_sectors,
    roaming_partner_id bigint
        constraint fk_cdr_sms_roaming_partner
            references public.roaming_partners,
    event_time         timestamp              not null,
    sms_type           text                   not null,
    direction          text                   not null
        constraint chk_cdr_sms_direction
            check (direction = ANY (ARRAY ['outbound'::text, 'inbound'::text])),
    charge_amount      numeric default 0.0000 not null
);

alter table public.usage_cdr_sms
    owner to postgres;

create index idx_subscriptionid_usage_cdr_sms
    on public.usage_cdr_sms (subscription_id);

create table public.usage_cdr_data
(
    data_cdr_id        bigserial
        primary key,
    subscription_id    bigint                 not null
        constraint fk_cdr_data_subscription
            references public.subscriptions,
    sector_id          bigint
        constraint fk_cdr_data_sector
            references public.tower_sectors,
    roaming_partner_id bigint
        constraint fk_cdr_data_roaming_partner
            references public.roaming_partners,
    session_start      timestamp              not null,
    session_end        timestamp              not null,
    data_used_mb       numeric default 0.0000 not null,
    apn                text,
    ip_address         inet,
    charge_amount      numeric default 0.0000 not null,
    constraint chk_cdr_data_times
        check (session_end >= session_start)
);

alter table public.usage_cdr_data
    owner to postgres;

create index idx_cdr_data_subscriptionid
    on public.usage_cdr_data (subscription_id);

create index idx_subscriptionid_usage_cdr_data
    on public.usage_cdr_data (subscription_id);

create table public.usage_aggregates_daily
(
    usage_daily_id      bigserial
        primary key,
    subscription_id     bigint                              not null
        constraint fk_usage_daily_subscription
            references public.subscriptions,
    usage_date          date                                not null,
    total_call_seconds  bigint    default 0                 not null,
    total_sms_count     integer   default 0                 not null,
    total_data_mb       numeric   default 0.0000            not null,
    total_charge_amount numeric   default 0.0000            not null,
    generated_at        timestamp default CURRENT_TIMESTAMP not null,
    constraint uq_usage_daily_subscription_date
        unique (subscription_id, usage_date)
);

alter table public.usage_aggregates_daily
    owner to postgres;

create table public.invoices
(
    invoice_id           bigserial
        primary key,
    account_id           bigint                         not null
        constraint fk_invoices_account
            references public.accounts,
    invoice_number       text                           not null
        unique,
    billing_period_start date                           not null,
    billing_period_end   date                           not null,
    issue_date           date                           not null,
    due_date             date                           not null,
    total_amount         numeric default 0.00           not null,
    tax_amount           numeric default 0.00           not null,
    discount_amount      numeric default 0.00           not null,
    status               text    default 'issued'::text not null,
    constraint chk_invoices_period
        check (billing_period_end >= billing_period_start),
    constraint chk_invoices_due_date
        check (due_date >= issue_date)
);

alter table public.invoices
    owner to postgres;

create index idx_invoice_accountid
    on public.invoices (account_id);

create table public.invoice_items
(
    invoice_item_id bigserial
        primary key,
    invoice_id      bigint               not null
        constraint fk_invoice_items_invoice
            references public.invoices,
    subscription_id bigint
        constraint fk_invoice_items_subscription
            references public.subscriptions,
    item_type       text                 not null,
    description     text,
    quantity        numeric default 1    not null,
    unit_price      numeric default 0.00 not null,
    line_amount     numeric default 0.00 not null,
    tax_rate        numeric default 0.00 not null
);

alter table public.invoice_items
    owner to postgres;

create index idx_inv_items_invoiceid
    on public.invoice_items (invoice_id);

create table public.payment_methods
(
    payment_method_id bigserial
        primary key,
    method_name       text                           not null
        unique,
    provider_name     text,
    is_online         boolean default false          not null,
    status            text    default 'active'::text not null
);

alter table public.payment_methods
    owner to postgres;

create table public.payments
(
    payment_id        bigserial
        primary key,
    account_id        bigint                         not null
        constraint fk_payments_account
            references public.accounts,
    invoice_id        bigint
        constraint fk_payments_invoice
            references public.invoices,
    payment_method_id bigint
        constraint fk_payments_method
            references public.payment_methods,
    payment_date      timestamp                      not null,
    amount            numeric                        not null
        constraint chk_payments_amount
            check (amount >= (0)::numeric),
    reference_number  text,
    status            text default 'completed'::text not null
);

alter table public.payments
    owner to postgres;

create index idx_payments_invoiceid
    on public.payments (invoice_id);

create index idx_accountid_payments
    on public.payments (account_id);

create table public.billing_runs
(
    billing_run_id           bigserial
        primary key,
    billing_cycle_id         bigint                          not null
        constraint fk_billing_runs_cycle
            references public.billing_cycles,
    period_start             date                            not null,
    period_end               date                            not null,
    run_started_at           timestamp                       not null,
    run_finished_at          timestamp,
    status                   text    default 'running'::text not null,
    generated_invoices_count integer default 0               not null,
    constraint chk_billing_runs_period
        check (period_end >= period_start),
    constraint chk_billing_runs_times
        check ((run_finished_at IS NULL) OR (run_finished_at >= run_started_at))
);

alter table public.billing_runs
    owner to postgres;

create table public.crm_tickets
(
    ticket_id            bigserial
        primary key,
    customer_id          bigint                              not null
        constraint fk_crm_tickets_customer
            references public.customers,
    account_id           bigint
        constraint fk_crm_tickets_account
            references public.accounts,
    subscription_id      bigint
        constraint fk_crm_tickets_subscription
            references public.subscriptions,
    assigned_employee_id bigint                              not null
        constraint fk_crm_tickets_employee
            references public.employees,
    ticket_type          text                                not null,
    subject              text                                not null,
    description          text,
    priority             text      default 'medium'::text    not null
        constraint check_priority
            check (priority = ANY (ARRAY ['low'::text, 'medium'::text, 'high'::text, 'critical'::text])),
    status               text      default 'open'::text      not null
        constraint check_name
            check (status = ANY
                   (ARRAY ['open'::text, 'in_progress'::text, 'escalated'::text, 'resolved'::text, 'closed'::text])),
    created_at           timestamp default CURRENT_TIMESTAMP not null,
    closed_at            timestamp,
    parent_ticket_id     bigint
        constraint fk_crm_tickets_parent
            references public.crm_tickets,
    constraint chk_crm_tickets_times
        check ((closed_at IS NULL) OR (closed_at >= created_at))
);

alter table public.crm_tickets
    owner to postgres;

create table public.crm_interactions
(
    interaction_id   bigserial
        primary key,
    ticket_id        bigint                              not null
        constraint fk_crm_interactions_ticket
            references public.crm_tickets,
    employee_id      bigint
        constraint fk_crm_interactions_employee
            references public.employees,
    interaction_type text                                not null,
    channel          text                                not null,
    interaction_time timestamp default CURRENT_TIMESTAMP not null,
    notes            text,
    old_status       text,
    new_status       text
);

alter table public.crm_interactions
    owner to postgres;

create index idx_ticketid_crm_i
    on public.crm_interactions (ticket_id);

create table public.employee_assignments
(
    assignment_id   bigserial
        primary key,
    employee_id     bigint                        not null
        constraint fk_employee_assignments_employee
            references public.employees,
    ticket_id       bigint
        constraint fk_employee_assignments_ticket
            references public.crm_tickets,
    outage_id       bigint
        constraint fk_employee_assignments_outage
            references public.outages,
    assignment_type text                          not null,
    start_time      timestamp                     not null,
    end_time        timestamp,
    status          text default 'assigned'::text not null,
    constraint chk_employee_assignments_times
        check ((end_time IS NULL) OR (end_time >= start_time))
);

alter table public.employee_assignments
    owner to postgres;

create table public.sim_card_subscription_history
(
    sim_card_subscription_history_id bigint default nextval('sim_card_subscription_history_sim_card_subscription_history_seq'::regclass) not null
        primary key,
    sim_id                           bigint
        constraint fk_sim_history_sim
            references public.sim_cards,
    subscription_id                  bigint                                                                                              not null
        constraint fk_sim_history_subscription
            references public.subscriptions,
    start_date                       timestamp                                                                                           not null,
    end_date                         timestamp,
    constraint chk_sim_history_dates
        check ((end_date IS NULL) OR (end_date >= start_date))
);

alter table public.sim_card_subscription_history
    owner to postgres;

create index idx_subsid_sim_c_subs_hist
    on public.sim_card_subscription_history (subscription_id);

create table public.plan_services
(
    plan_service_id bigserial
        primary key,
    plan_id         bigint                         not null
        constraint fk_plan_services_plan
            references public.plans,
    service_id      bigint                         not null
        constraint fk_plan_services_service
            references public.services,
    allowance_value numeric,
    allowance_unit  text,
    is_unlimited    boolean default false          not null,
    is_included     boolean default true           not null,
    status          text    default 'active'::text not null,
    constraint uq_plan_services_plan_service
        unique (plan_id, service_id)
);

alter table public.plan_services
    owner to postgres;