create table customer_types
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table customer_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table address_types
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table account_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table contract_types
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table contract_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table department_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table employment_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table device_types
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table sim_types
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table sim_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table service_categories
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table product_types
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table product_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table overage_policy_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table plan_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table subscription_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table addon_types
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table allowance_units
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table addon_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table subscription_addon_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table device_assignment_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table network_generations
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table network_technology_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table network_site_types
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table network_site_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table tower_ownership_types
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table tower_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table frequency_bands
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table tower_sector_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table coverage_types
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table roaming_partner_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table network_alarm_types
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table alarm_severities
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table network_alarm_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table outage_types
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table outage_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table call_types
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table usage_directions
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table sms_types
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table invoice_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table invoice_item_types
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table payment_method_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table payment_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table billing_run_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table crm_ticket_types
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table ticket_priorities
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table crm_ticket_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table interaction_types
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table interaction_channels
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table employee_assignment_types
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table employee_assignment_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table plan_service_statuses
(
    code        text primary key,
    description text,
    is_active   boolean default true not null
);

create table customers
(
    customer_id   bigserial
        primary key,
    customer_type text                                not null
        constraint fk_customers_customer_type
            references customer_types (code) on delete restrict on update restrict,
    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 fk_customers_status
            references customer_statuses (code) on delete set default on update restrict,
    created_at    timestamptz default CURRENT_TIMESTAMP not null,
    updated_at    timestamptz 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
                (first_name IS NOT NULL) AND (last_name IS NOT NULL) AND
                (company_name IS NOT NULL)))
);

create table customer_addresses
(
    address_id   bigserial
        primary key,
    customer_id  bigint                              not null
        constraint fk_customer_addresses_customer
            references customers on delete restrict on update restrict,
    address_type text                                not null
        constraint fk_customer_addresses_address_type
            references address_types (code) on delete restrict on update restrict,
    country      text                                not null,
    city         text                                not null,
    street       text                                not null,
    postal_code  text,
    latitude     numeric,
    longitude    numeric,
    location     geometry(Point, 4326)
        generated always as
        (
            case
                when latitude is null then null
                else ST_SetSRID(
                    ST_MakePoint(longitude::double precision, latitude::double precision),
                    4326
                )
            end
        ) stored,
    is_primary   boolean   default false             not null,
    created_at   timestamptz default CURRENT_TIMESTAMP not null,
    constraint chk_customer_addresses_latitude
        check ((latitude IS NULL) OR (latitude between -90 and 90)),
    constraint chk_customer_addresses_longitude
        check ((longitude IS NULL) OR (longitude between -180 and 180)),
    constraint chk_customer_addresses_coordinates_pair
        check ((latitude IS NULL) = (longitude IS NULL))
);

create table 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       timestamptz default CURRENT_TIMESTAMP not null
);

create table accounts
(
    account_id       bigserial
        primary key,
    customer_id      bigint                              not null
        constraint fk_accounts_customer
            references customers on delete restrict on update restrict,
    account_number   text                                not null
        unique,
    account_status   text      default 'active'::text    not null
        constraint fk_accounts_account_status
            references account_statuses (code) on delete set default on update restrict,
    credit_limit     numeric   default 0.00              not null
        constraint chk_accounts_credit_limit
            check (credit_limit >= (0)::numeric),
    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 billing_cycles on delete set null on update restrict,
    created_at       timestamptz default CURRENT_TIMESTAMP not null,
    updated_at       timestamptz default CURRENT_TIMESTAMP not null,
    constraint uq_accounts_customer
        unique (customer_id),
    constraint uq_accounts_account_customer
        unique (account_id, customer_id)
);

create table contracts
(
    contract_id     bigserial
        primary key,
    customer_id     bigint                         not null
        constraint fk_contracts_customer
            references customers on delete restrict on update restrict,
    account_id      bigint                         not null
        constraint fk_contracts_account
            references accounts on delete restrict on update restrict,
    contract_type   text                           not null
        constraint fk_contracts_contract_type
            references contract_types (code) on delete restrict on update restrict,
    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
        constraint fk_contracts_status
            references contract_statuses (code) on delete set default on update restrict,
    signed_at       timestamptz,
    constraint chk_contracts_dates
        check ((end_date IS NULL) OR (end_date >= start_date)),
    constraint uq_contracts_contract_account
        unique (contract_id, account_id),
    constraint fk_contracts_account_customer
        foreign key (account_id, customer_id)
            references accounts (account_id, customer_id) on delete restrict on update restrict
);

create table departments
(
    department_id   bigserial
        primary key,
    department_name text                        not null
        unique,
    location        text,
    status          text default 'active'::text not null
        constraint fk_departments_status
            references department_statuses (code) on delete set default on update restrict
);

create table 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
);

create table employees
(
    employee_id       bigserial
        primary key,
    department_id     bigint                      not null
        constraint fk_employees_department
            references departments on delete restrict on update restrict,
    role_id           bigint                      not null
        constraint fk_employees_role
            references employee_roles on delete restrict on update restrict,
    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
        constraint fk_employees_employment_status
            references employment_statuses (code) on delete set default on update restrict,
    manager_id        bigint
        constraint fk_employees_manager
            references employees on delete set null on update restrict,
    constraint chk_employees_not_own_manager
        check ((manager_id IS NULL) OR (manager_id <> employee_id))
);

create table devices
(
    device_id     bigserial
        primary key,
    imei          text                           not null
        unique,
    serial_number text                           not null
        unique,
    manufacturer  text,
    model         text,
    device_type   text
        constraint fk_devices_device_type
            references device_types (code) on delete set null on update restrict,
    purchase_date date
);

create table 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 fk_sim_cards_sim_type
            references sim_types (code) on delete restrict on update restrict,
    pin_code  text,
    puk_code  text,
    status    text default 'available'::text not null
        constraint fk_sim_cards_status
            references sim_statuses (code) on delete set default on update restrict,
    issued_at timestamptz
);

create table services
(
    service_id       bigserial
        primary key,
    service_name     text                                not null
        unique,
    service_category text                                not null
        constraint fk_services_service_category
            references service_categories (code) on delete restrict on update restrict,
    description      text,
    is_active        boolean   default true              not null,
    created_at       timestamptz default CURRENT_TIMESTAMP not null
);

create table products
(
    product_id   bigserial
        primary key,
    product_name text                                not null,
    product_code text                                not null
        unique,
    product_type text                                not null
        constraint fk_products_product_type
            references product_types (code) on delete restrict on update restrict,
    description  text,
    status       text      default 'active'::text    not null
        constraint fk_products_status
            references product_statuses (code) on delete set default on update restrict,
    created_at   timestamptz default CURRENT_TIMESTAMP not null
);

create table overage_policies
(
    overage_policy_id    bigserial
        primary key,
    policy_name          text                           not null
        unique,
    voice_rate_per_min   numeric default 0.0000         not null
        constraint chk_overage_voice_rate
            check (voice_rate_per_min >= 0),
    sms_rate             numeric default 0.0000         not null
        constraint chk_overage_sms_rate
            check (sms_rate >= 0),
    data_rate_per_mb     numeric default 0.0000         not null
        constraint chk_overage_data_rate
            check (data_rate_per_mb >= 0),
    throttle_after_limit boolean default false          not null,
    fair_use_limit_mb    numeric
        constraint chk_overage_fair_use_limit
            check ((fair_use_limit_mb IS NULL) OR (fair_use_limit_mb >= 0)),
    status               text    default 'active'::text not null
        constraint fk_overage_policies_status
            references overage_policy_statuses (code) on delete set default on update restrict
);

create table plans
(
    plan_id              bigserial
        primary key,
    product_id           bigint                         not null
        constraint fk_plans_product
            references products on delete restrict on update restrict,
    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 overage_policies on delete set null on update restrict,
    contract_term_months integer default 0              not null
        constraint chk_plans_term
            check (contract_term_months >= 0),
    status               text    default 'active'::text not null
        constraint fk_plans_status
            references plan_statuses (code) on delete set default on update restrict
);

create table subscriptions
(
    subscription_id     bigserial
        primary key,
    account_id          bigint                      not null
        constraint fk_subscriptions_account
            references accounts on delete restrict on update restrict,
    plan_id             bigint                      not null
        constraint fk_subscriptions_plan
            references plans on delete restrict on update restrict,
    contract_id         bigint
        constraint fk_subscriptions_contract
            references contracts on delete set null on update restrict,
    subscription_number text                        not null
        unique,
    activation_date     date                        not null,
    end_date            date,
    status              text default 'active'::text not null
        constraint fk_subscriptions_status
            references subscription_statuses (code) on delete set default on update restrict,
    billing_start_date  date                        not null,
    constraint chk_subscriptions_dates
        check ((end_date IS NULL) OR (end_date >= activation_date)),
    constraint chk_subscriptions_billing_start
        check (billing_start_date <= activation_date),
    constraint chk_subscriptions_end_month_end
        check ((end_date IS NULL) OR (extract(day from (end_date + 1)) = 1)),
    constraint uq_subscriptions_subscription_account
        unique (subscription_id, account_id),
    constraint fk_subscriptions_contract_account
        foreign key (contract_id, account_id)
            references contracts (contract_id, account_id) on delete restrict on update restrict
);

create table addons
(
    addon_id        bigserial
        primary key,
    addon_name      text                           not null,
    addon_type      text                           not null
        constraint fk_addons_addon_type
            references addon_types (code) on delete restrict on update restrict,
    price           numeric default 0.00           not null
        constraint chk_addons_price
            check (price >= 0),
    allowance_value numeric
        constraint chk_addons_allowance_value
            check ((allowance_value IS NULL) OR (allowance_value >= 0)),
    allowance_unit  text
        constraint fk_addons_allowance_unit
            references allowance_units (code) on delete set null on update restrict,
    is_recurring    boolean default true           not null,
    status          text    default 'active'::text not null
        constraint fk_addons_status
            references addon_statuses (code) on delete set default on update restrict
);

create table subscription_addons
(
    subscription_addon_id bigserial
        primary key,
    subscription_id       bigint                         not null
        constraint fk_subscription_addons_subscription
            references subscriptions on delete restrict on update restrict,
    addon_id              bigint                         not null
        constraint fk_subscription_addons_addon
            references addons on delete restrict on update restrict,
    activation_date       date                           not null,
    deactivation_date     date,
    status                text    default 'active'::text not null
        constraint fk_subscription_addons_status
            references subscription_addon_statuses (code) on delete set default on update restrict,
    price_at_activation   numeric default 0.00           not null
        constraint chk_subscription_addons_price
            check (price_at_activation >= 0),
    constraint chk_subscription_addons_dates
        check ((deactivation_date IS NULL) OR (deactivation_date >= activation_date)),
    constraint chk_subscription_addons_status_date
        check ((status = 'active'::text) = (deactivation_date IS NULL))
);

create table subscription_status_history
(
    status_history_id      bigserial
        primary key,
    subscription_id        bigint                              not null
        constraint fk_sub_status_history_subscription
            references subscriptions on delete restrict on update restrict,
    old_status             text
        constraint fk_subscription_status_history_old_status
            references subscription_statuses (code) on delete set null on update restrict,
    new_status             text                                not null
        constraint fk_subscription_status_history_new_status
            references subscription_statuses (code) on delete restrict on update restrict,
    changed_at             timestamptz default CURRENT_TIMESTAMP not null,
    changed_by_employee_id bigint
        constraint fk_sub_status_history_employee
            references employees on delete set null on update restrict,
    reason                 text
);

create table device_assignments
(
    device_assignment_id bigserial
        primary key,
    device_id            bigint                      not null
        constraint fk_device_assignments_device
            references devices on delete restrict on update restrict,
    subscription_id      bigint                      not null
        constraint fk_device_assignments_subscription
            references subscriptions on delete restrict on update restrict,
    assigned_from        timestamptz                   not null,
    assigned_to          timestamptz,
    assignment_status    text default 'active'::text not null
        constraint fk_device_assignments_assignment_status
            references device_assignment_statuses (code) on delete set default on update restrict,
    notes                text,
    constraint chk_device_assignments_times
        check ((assigned_to IS NULL) OR (assigned_to > assigned_from)),
    constraint chk_device_assignments_status_time
        check ((assignment_status = 'active'::text) = (assigned_to IS NULL)),
    constraint ex_device_assignments_no_overlap
        exclude using gist
        (device_id with =,
         tstzrange(assigned_from, coalesce(assigned_to, 'infinity'::timestamptz), '[)') with &&)
);

create table network_technologies
(
    technology_id   bigserial
        primary key,
    technology_name text                        not null
        unique,
    generation      text                        not null
        constraint fk_network_technologies_generation
            references network_generations (code) on delete restrict on update restrict,
    description     text,
    status          text default 'active'::text not null
        constraint fk_network_technologies_status
            references network_technology_statuses (code) on delete set default on update restrict
);

create table 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,
    location  geometry(Point, 4326)
        generated always as
        (
            case
                when latitude is null then null
                else ST_SetSRID(
                    ST_MakePoint(longitude::double precision, latitude::double precision),
                    4326
                )
            end
        ) stored,
    site_type text
        constraint fk_network_sites_site_type
            references network_site_types (code) on delete set null on update restrict,
    status    text default 'active'::text not null
        constraint fk_network_sites_status
            references network_site_statuses (code) on delete set default on update restrict,
    opened_at timestamptz,
    constraint chk_network_sites_latitude
        check ((latitude IS NULL) OR (latitude between -90 and 90)),
    constraint chk_network_sites_longitude
        check ((longitude IS NULL) OR (longitude between -180 and 180)),
    constraint chk_network_sites_coordinates_pair
        check ((latitude IS NULL) = (longitude IS NULL))
);

create table cell_towers
(
    tower_id          bigserial
        primary key,
    site_id           bigint                      not null
        constraint fk_cell_towers_site
            references network_sites on delete restrict on update restrict,
    tower_code        text                        not null
        unique,
    height_meters     numeric
        constraint chk_cell_towers_height
            check ((height_meters IS NULL) OR (height_meters >= 0)),
    ownership_type    text
        constraint fk_cell_towers_ownership_type
            references tower_ownership_types (code) on delete set null on update restrict,
    installation_date date,
    status            text default 'active'::text not null
        constraint fk_cell_towers_status
            references tower_statuses (code) on delete set default on update restrict,
    vendor_name       text
);

create table tower_sectors
(
    sector_id      bigserial
        primary key,
    tower_id       bigint                      not null
        constraint fk_tower_sectors_tower
            references cell_towers on delete restrict on update restrict,
    sector_label   text                        not null,
    azimuth        integer
        constraint chk_tower_sectors_azimuth
            check ((azimuth IS NULL) OR ((azimuth >= 0) AND (azimuth <= 359))),
    beamwidth      integer
        constraint chk_tower_sectors_beamwidth
            check ((beamwidth >= 1) AND (beamwidth <= 360)),
    frequency_band text
        constraint fk_tower_sectors_frequency_band
            references frequency_bands (code) on delete set null on update restrict,
    technology_id  bigint                      not null
        constraint fk_tower_sectors_technology
            references network_technologies on delete restrict on update restrict,
    status         text default 'active'::text not null
        constraint fk_tower_sectors_status
            references tower_sector_statuses (code) on delete set default on update restrict,
    constraint uq_tower_sector_label
        unique (tower_id, sector_label)
);

create table coverage_zones
(
    coverage_zone_id     bigserial
        primary key,
    sector_id            bigint not null
        constraint fk_coverage_zones_sector
            references tower_sectors on delete restrict on update restrict,
    coverage_type        text   not null
        constraint fk_coverage_zones_coverage_type
            references coverage_types (code) on delete restrict on update restrict,
    zone_description     text,
    coverage_radius_m    integer default 1500 not null
        constraint chk_coverage_zones_radius
            check (coverage_radius_m between 100 and 50000),
    coverage_area        geometry(Polygon, 4326),
    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     timestamptz
);

create table 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 fk_roaming_partners_status
            references roaming_partner_statuses (code) on delete set default on update restrict,
    constraint chk_roaming_partners_dates
        check ((agreement_end IS NULL) OR
               ((agreement_start IS NOT NULL) AND (agreement_end >= agreement_start)))
);

create table network_alarms
(
    alarm_id    bigserial
        primary key,
    tower_id    bigint
        constraint fk_network_alarms_tower
            references cell_towers on delete set null on update restrict,
    sector_id   bigint
        constraint fk_network_alarms_sector
            references tower_sectors on delete set null on update restrict,
    alarm_type  text                      not null
        constraint fk_network_alarms_alarm_type
            references network_alarm_types (code) on delete restrict on update restrict,
    severity    text                      not null
        constraint fk_network_alarms_severity
            references alarm_severities (code) on delete restrict on update restrict,
    raised_at   timestamptz                 not null,
    cleared_at  timestamptz,
    status      text default 'open'::text not null
        constraint fk_network_alarms_status
            references network_alarm_statuses (code) on delete set default on update restrict,
    description text,
    constraint chk_network_alarms_times
        check ((cleared_at IS NULL) OR (cleared_at >= raised_at)),
    constraint chk_network_alarms_status_time
        check ((status = 'cleared'::text) = (cleared_at IS NOT NULL)),
    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)))
);

create table outages
(
    outage_id   bigserial
        primary key,
    site_id     bigint                    not null
        constraint fk_outages_site
            references network_sites on delete restrict on update restrict,
    alarm_id    bigint
        constraint fk_outages_alarm
            references network_alarms on delete set null on update restrict,
    outage_type text                      not null
        constraint fk_outages_outage_type
            references outage_types (code) on delete restrict on update restrict,
    start_time  timestamptz                 not null,
    end_time    timestamptz,
    status      text default 'open'::text not null
        constraint fk_outages_status
            references outage_statuses (code) on delete set default on update restrict,
    root_cause  text,
    constraint chk_outages_times
        check ((end_time IS NULL) OR (end_time >= start_time)),
    constraint chk_outages_status_time
        check ((status = 'open'::text) = (end_time IS NULL))
);

create table usage_cdr_calls
(
    call_cdr_id        bigserial
        primary key,
    subscription_id    bigint                 not null
        constraint fk_cdr_calls_subscription
            references subscriptions on delete restrict on update restrict,
    originating_msisdn text                   not null,
    destination_msisdn text                   not null,
    sector_id          bigint
        constraint fk_cdr_calls_sector
            references tower_sectors on delete set null on update restrict,
    roaming_partner_id bigint
        constraint fk_cdr_calls_roaming_partner
            references roaming_partners on delete set null on update restrict,
    event_start_time   timestamptz              not null,
    event_end_time     timestamptz              not null,
    duration_seconds   integer                not null
        constraint chk_cdr_calls_duration
            check (duration_seconds >= 0),
    call_type          text                   not null
        constraint fk_usage_cdr_calls_call_type
            references call_types (code) on delete restrict on update restrict,
    direction          text                   not null
        constraint fk_usage_cdr_calls_direction
            references usage_directions (code) on delete restrict on update restrict,
    charge_amount      numeric default 0.0000 not null
        constraint chk_cdr_calls_charge_amount
            check (charge_amount >= 0),
    fraud_score        numeric default 0.00
        constraint chk_cdr_calls_fraud_score_nonnegative
            check ((fraud_score IS NULL) OR (fraud_score >= 0)),
    constraint chk_cdr_calls_times
        check (event_end_time >= event_start_time)
);

create table usage_cdr_sms
(
    sms_cdr_id         bigserial
        primary key,
    subscription_id    bigint                 not null
        constraint fk_cdr_sms_subscription
            references subscriptions on delete restrict on update restrict,
    source_msisdn      text                   not null,
    destination_msisdn text                   not null,
    sector_id          bigint
        constraint fk_cdr_sms_sector
            references tower_sectors on delete set null on update restrict,
    roaming_partner_id bigint
        constraint fk_cdr_sms_roaming_partner
            references roaming_partners on delete set null on update restrict,
    event_time         timestamptz              not null,
    sms_type           text                   not null
        constraint fk_usage_cdr_sms_sms_type
            references sms_types (code) on delete restrict on update restrict,
    direction          text                   not null
        constraint fk_usage_cdr_sms_direction
            references usage_directions (code) on delete restrict on update restrict,
    charge_amount      numeric default 0.0000 not null
        constraint chk_cdr_sms_charge_amount
            check (charge_amount >= 0)
);

create table usage_cdr_data
(
    data_cdr_id        bigserial
        primary key,
    subscription_id    bigint                 not null
        constraint fk_cdr_data_subscription
            references subscriptions on delete restrict on update restrict,
    sector_id          bigint
        constraint fk_cdr_data_sector
            references tower_sectors on delete set null on update restrict,
    roaming_partner_id bigint
        constraint fk_cdr_data_roaming_partner
            references roaming_partners on delete set null on update restrict,
    session_start      timestamptz              not null,
    session_end        timestamptz              not null,
    data_used_mb       numeric default 0.0000 not null
        constraint chk_cdr_data_used_mb
            check (data_used_mb >= 0),
    apn                text,
    ip_address         inet,
    charge_amount      numeric default 0.0000 not null
        constraint chk_cdr_data_charge_amount
            check (charge_amount >= 0),
    constraint chk_cdr_data_times
        check (session_end >= session_start)
);

create table usage_aggregates_daily
(
    usage_daily_id      bigserial
        primary key,
    subscription_id     bigint                              not null
        constraint fk_usage_daily_subscription
            references subscriptions on delete restrict on update restrict,
    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        timestamptz default CURRENT_TIMESTAMP not null,
    constraint chk_usage_daily_call_seconds
        check (total_call_seconds >= 0),
    constraint chk_usage_daily_sms_count
        check (total_sms_count >= 0),
    constraint chk_usage_daily_data_mb
        check (total_data_mb >= 0),
    constraint chk_usage_daily_charge_amount
        check (total_charge_amount >= 0),
    constraint uq_usage_daily_subscription_date
        unique (subscription_id, usage_date)
);

create table invoices
(
    invoice_id           bigserial
        primary key,
    account_id           bigint                         not null
        constraint fk_invoices_account
            references accounts on delete restrict on update restrict,
    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 fk_invoices_status
            references invoice_statuses (code) on delete set default on update restrict,
    constraint chk_invoices_total_amount
        check (total_amount >= 0),
    constraint chk_invoices_tax_amount
        check (tax_amount >= 0),
    constraint chk_invoices_discount_amount
        check (discount_amount >= 0),
    constraint chk_invoices_period
        check (billing_period_end >= billing_period_start),
    constraint chk_invoices_due_date
        check (due_date >= issue_date),
    constraint uq_invoices_invoice_account
        unique (invoice_id, account_id)
);

create table invoice_items
(
    invoice_item_id bigserial
        primary key,
    invoice_id      bigint               not null
        constraint fk_invoice_items_invoice
            references invoices on delete restrict on update restrict,
    subscription_id bigint
        constraint fk_invoice_items_subscription
            references subscriptions on delete set null on update restrict,
    item_type       text                 not null
        constraint fk_invoice_items_item_type
            references invoice_item_types (code) on delete restrict on update restrict,
    description     text,
    quantity        numeric default 1    not null
        constraint chk_invoice_items_quantity
            check (quantity >= 0),
    unit_price      numeric default 0.00 not null
        constraint chk_invoice_items_unit_price
            check (unit_price >= 0),
    line_amount     numeric default 0.00 not null
        constraint chk_invoice_items_line_amount
            check (line_amount >= 0),
    tax_rate        numeric default 0.00 not null
        constraint chk_invoice_items_tax_rate
            check (tax_rate >= 0)
);

create table 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
        constraint fk_payment_methods_status
            references payment_method_statuses (code) on delete set default on update restrict
);

create table payments
(
    payment_id        bigserial
        primary key,
    account_id        bigint                         not null
        constraint fk_payments_account
            references accounts on delete restrict on update restrict,
    invoice_id        bigint
        constraint fk_payments_invoice
            references invoices on delete set null on update restrict,
    payment_method_id bigint
        constraint fk_payments_method
            references payment_methods on delete set null on update restrict,
    payment_date      timestamptz                      not null,
    amount            numeric                        not null
        constraint chk_payments_amount
            check (amount >= (0)::numeric),
    reference_number  text,
    status            text default 'completed'::text not null
        constraint fk_payments_status
            references payment_statuses (code) on delete set default on update restrict,
    constraint fk_payments_invoice_account
        foreign key (invoice_id, account_id)
            references invoices (invoice_id, account_id) on delete restrict on update restrict
);

create table billing_runs
(
    billing_run_id           bigserial
        primary key,
    billing_cycle_id         bigint                          not null
        constraint fk_billing_runs_cycle
            references billing_cycles on delete restrict on update restrict,
    period_start             date                            not null,
    period_end               date                            not null,
    run_started_at           timestamptz                       not null,
    run_finished_at          timestamptz,
    status                   text    default 'running'::text not null
        constraint fk_billing_runs_status
            references billing_run_statuses (code) on delete set default on update restrict,
    generated_invoices_count integer default 0               not null
        constraint chk_billing_runs_generated_count
            check (generated_invoices_count >= 0),
    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)),
    constraint chk_billing_runs_status_time
        check ((status = 'running'::text) = (run_finished_at IS NULL))
);

create table crm_tickets
(
    ticket_id            bigserial
        primary key,
    customer_id          bigint                              not null
        constraint fk_crm_tickets_customer
            references customers on delete restrict on update restrict,
    account_id           bigint
        constraint fk_crm_tickets_account
            references accounts on delete set null on update restrict,
    subscription_id      bigint
        constraint fk_crm_tickets_subscription
            references subscriptions on delete set null on update restrict,
    assigned_employee_id bigint                              not null
        constraint fk_crm_tickets_employee
            references employees on delete restrict on update restrict,
    ticket_type          text                                not null
        constraint fk_crm_tickets_ticket_type
            references crm_ticket_types (code) on delete restrict on update restrict,
    subject              text                                not null,
    description          text,
    priority             text      default 'medium'::text    not null
        constraint fk_crm_tickets_priority
            references ticket_priorities (code) on delete set default on update restrict,
    status               text      default 'open'::text      not null
        constraint fk_crm_tickets_status
            references crm_ticket_statuses (code) on delete set default on update restrict,
    created_at           timestamptz default CURRENT_TIMESTAMP not null,
    closed_at            timestamptz,
    parent_ticket_id     bigint
        constraint fk_crm_tickets_parent
            references crm_tickets on delete set null on update restrict,
    constraint chk_crm_tickets_times
        check ((closed_at IS NULL) OR (closed_at >= created_at)),
    constraint chk_crm_tickets_status_time
        check ((status = 'closed'::text) = (closed_at IS NOT NULL)),
    constraint chk_crm_tickets_not_own_parent
        check ((parent_ticket_id IS NULL) OR (parent_ticket_id <> ticket_id)),
    constraint chk_crm_tickets_subscription_requires_account
        check ((subscription_id IS NULL) OR (account_id IS NOT NULL)),
    constraint fk_crm_tickets_account_customer
        foreign key (account_id, customer_id)
            references accounts (account_id, customer_id) on delete restrict on update restrict,
    constraint fk_crm_tickets_subscription_account
        foreign key (subscription_id, account_id)
            references subscriptions (subscription_id, account_id) on delete set null on update restrict
);

create table crm_interactions
(
    interaction_id   bigserial
        primary key,
    ticket_id        bigint                              not null
        constraint fk_crm_interactions_ticket
            references crm_tickets on delete restrict on update restrict,
    employee_id      bigint
        constraint fk_crm_interactions_employee
            references employees on delete set null on update restrict,
    interaction_type text                                not null
        constraint fk_crm_interactions_interaction_type
            references interaction_types (code) on delete restrict on update restrict,
    channel          text                                not null
        constraint fk_crm_interactions_channel
            references interaction_channels (code) on delete restrict on update restrict,
    interaction_time timestamptz default CURRENT_TIMESTAMP not null,
    notes            text,
    old_status       text
        constraint fk_crm_interactions_old_status
            references crm_ticket_statuses (code) on delete set null on update restrict,
    new_status       text
        constraint fk_crm_interactions_new_status
            references crm_ticket_statuses (code) on delete set null on update restrict
);

create table employee_assignments
(
    assignment_id   bigserial
        primary key,
    employee_id     bigint                        not null
        constraint fk_employee_assignments_employee
            references employees on delete restrict on update restrict,
    ticket_id       bigint
        constraint fk_employee_assignments_ticket
            references crm_tickets on delete set null on update restrict,
    outage_id       bigint
        constraint fk_employee_assignments_outage
            references outages on delete set null on update restrict,
    assignment_type text                          not null
        constraint fk_employee_assignments_assignment_type
            references employee_assignment_types (code) on delete restrict on update restrict,
    start_time      timestamptz                     not null,
    end_time        timestamptz,
    status          text default 'assigned'::text not null
        constraint fk_employee_assignments_status
            references employee_assignment_statuses (code) on delete set default on update restrict,
    constraint chk_employee_assignments_times
        check ((end_time IS NULL) OR (end_time >= start_time)),
    constraint chk_employee_assignments_exactly_one_target
        check (((ticket_id IS NOT NULL) AND (outage_id IS NULL)) OR
               ((ticket_id IS NULL) AND (outage_id IS NOT NULL)))
);

create table sim_card_subscription_history
(
    sim_card_subscription_history_id bigserial
        primary key,
    sim_id                           bigint                                                                                              not null
        constraint fk_sim_history_sim
            references sim_cards on delete restrict on update restrict,
    subscription_id                  bigint                                                                                              not null
        constraint fk_sim_history_subscription
            references subscriptions on delete restrict on update restrict,
    start_date                       timestamptz                                                                                           not null,
    end_date                         timestamptz,
    constraint chk_sim_history_dates
        check ((end_date IS NULL) OR (end_date > start_date)),
    constraint ex_sim_history_no_overlap
        exclude using gist
        (sim_id with =,
         tstzrange(start_date, coalesce(end_date, 'infinity'::timestamptz), '[)') with &&)
);

create table plan_services
(
    plan_service_id bigserial
        primary key,
    plan_id         bigint                         not null
        constraint fk_plan_services_plan
            references plans on delete restrict on update restrict,
    service_id      bigint                         not null
        constraint fk_plan_services_service
            references services on delete restrict on update restrict,
    allowance_value numeric
        constraint chk_plan_services_allowance_value
            check ((allowance_value IS NULL) OR (allowance_value >= 0)),
    allowance_unit  text
        constraint fk_plan_services_allowance_unit
            references allowance_units (code) on delete set null on update restrict,
    is_unlimited    boolean default false          not null,
    is_included     boolean default true           not null,
    status          text    default 'active'::text not null
        constraint fk_plan_services_status
            references plan_service_statuses (code) on delete set default on update restrict,
    constraint uq_plan_services_plan_service
        unique (plan_id, service_id)
);